SELECT COUNT(*)
FROM MTL_SAFETY_STOCKS s
-- INNER JOIN mtl_parameters e ON e.organization_id = s.organization_id --sn.organization_id
-- AND e.organization_code = 'T21'
INNER JOIN mtl_system_items_b b ON s.organization_id = b.organization_id
AND s.inventory_item_id = b.inventory_item_id
AND b.segment1 ='04G185009492'
2012年11月25日 星期日
SAP: OKOK
[上午 12:46:36] Adempiere/Compiere: 好
[上午 01:00:10] Adempiere/Compiere: 請試用
[上午 01:00:15] SAP: OKOK
[上午 01:01:40] SAP: 很多都有塞 0
[上午 01:01:46] SAP: 但, SS 還是沒有正確
[上午 01:01:54] Adempiere/Compiere: 不要嗎 ?
[上午 01:02:00] Adempiere/Compiere: 很多都有塞 0
[上午 01:02:22] Adempiere/Compiere: [但, SS 還是沒有正確] ?
[上午 01:02:22] SAP: SO, PO, OH, ... 可以不用塞, ....<== 要塞也 OK
[上午 01:02:28] SAP: 但, SS 還是不正確
[上午 01:02:54] Adempiere/Compiere: , SS 還是不正確 怎麼不正確 法?
[上午 01:02:59] Adempiere/Compiere: 哪裡出錯
[上午 01:04:37] *** SAP 傳送 IMG_26112012_010524.png ***
SS 是用 MDS 計算而來
[上午 01:04:48] SAP: 我查一下 TABLE
[上午 01:05:22] SAP: 像 ROW 72
[上午 01:05:34] SAP: 應該會有資料
[上午 01:05:44] SAP: 目前, 只有 04 G 有一筆 30
[上午 01:06:16] SAP: 這個情形跟剛剛 MDS 很類似
[上午 01:06:24] SAP: 剛 MDS 也是只有一筆
[上午 01:06:38] Adempiere/Compiere: 好
[上午 01:06:45] Adempiere/Compiere: 我看一下
[上午 01:11:16] SAP: 等一下
[上午 01:11:21] SAP: 是我的錯....
[上午 01:11:34] SAP: 應該是 SS 的 資料問題
[上午 01:12:17] SAP: 只要查: 04G185009492 有 SS 即可
[上午 01:16:11] *** 來自 SAP 的電話，通話時間 00:13。 ***
[上午 01:19:21] SAP: 我先 發 MAIL , 請 USER 測試
[上午 01:19:36] SAP: 明天還需要你協助 計算 Planned Order 的部份,
[上午 01:19:44] SAP: 先謝咯!!
[上午 01:26:09] Adempiere/Compiere: Ok
[下午 02:38:59] SAP: HIHI
[下午 02:39:02] SAP: HIHI
[下午 02:39:07] SAP: 有通嗎?
[下午 02:43:22] Adempiere/Compiere: HI
[下午 02:43:30] SAP: OKOK
[下午 02:43:38] Adempiere/Compiere: [只要查: 04G185009492 有 SS 即可]
[下午 02:44:22] SAP: mgln
2012年11月19日 星期一
groovy:ImportData
import groovy.sql.Sql
import java.sql.Timestamp
// sqlinformix = Sql.newInstance('jdbc:informix-sqli://192.168.170.211:1534/hisipddb:informixserver=hisipdsrv','informix','db@w94u6', 'com.informix.jdbc.IfxDriver' )
// sqlOracle = Sql.newInstance( 'jdbc:oracle:thin:@192.168.170.251:1521:orcl', 'adempiere','adempiere','oracle.jdbc.OracleDriver' )
//"jdbc:postgresql://hostname:port/dbname","username", "password");
sqlPostgres=Sql.newInstance('jdbc:postgresql://192.168.0.101:5433/adempiere', "adempiere", "adempiere","org.postgresql.Driver")
def m_created = new Timestamp(System.currentTimeMillis())
def table_id=0
def v_IsActive="Y"
sqlPostgres.eachRow( 'SELECT * FROM ADEMPIERE.M_Product' )
{
table_id=table_id+1
println "$it.Value -- ${it.Name} --"
sqlPostgres.execute(
'INSERT INTO ADEMPIERE.M_Product_T(M_Product_T_ID,AD_Client_ID,AD_Org_ID,Updated, UpdatedBy,Created,CreatedBy, IsActive,M_Product_ID,Value,Name,Description,C_UOM_ID,M_Product_Category_ID,C_TaxCategory_ID)'
+'VALUES ( ?, 11, 11, now(), 0, now(), 0, ?, ?, ?, ?, ?,?,?,? )' ,
[table_id, v_IsActive, it.M_Product_ID,it.Value,it.Name,it.Description,it.C_UOM_ID,it.M_Product_Category_ID,it.C_TaxCategory_ID] )
}
result=""
2012年11月18日 星期日
如何將文字串 output 到 cvs
如何將文字串 output 到 cvs
procedure proc_csv_output is
w_output_string long;
v_inv varchar2_varray;
w_title varchar2(10000);
inv_name varchar2(4000);
lv_desc1 varchar2(240);
lv_desc2 varchar2(240);
lv_class_code wip_discrete_jobs.class_code%type;
v_inv_names varchar2(4000);
v_isactive varchar2(1);
l_str varchar2(30);
begin
w_title := 'WO_Number,Class Code,EMS ,Item No,Description,OP,PrimaryItem,Description'
||',Subs Flag,ParentItem,Required,Issued,OpenQty,EMS_Onhand,WO_Shortage,Subs Qty,';
v_inv_names := '';
if inv_names.count > 0 then
for ix in 1 .. inv_names.count loop
if (inv_names(ix) is null or inv_names(ix) = '') then exit; end if;
v_inv_names := v_inv_names || inv_names(ix) || ',';
end loop;
end if;
dbms_output.put_line('g_csv_output.COUNT='||g_csv_output.count);
fnd_file.put_line(fnd_file.log,g_csv_output.count);
w_title := w_title || v_inv_names || 'PackMtl.PO';
dbms_output.put_line('w_title='||w_title);
fnd_file.put_line(2, w_title);
v_isactive := 'Y';
for i in 1 .. g_csv_output.count loop
if g_csv_output(i).isactive is not null then
v_isactive := g_csv_output(i).isactive;
end if;
if g_item is not null and g_item != '%' then
l_str := g_item||'%';
if g_csv_output(i).item_no not like l_str then
goto xx_next_record;
end if;
end if;
if (g_csv_output(i).job_no is not null and v_isactive='Y') then
w_output_string :=
g_csv_output(i).job_no || ',' ||
g_csv_output(i).lv_class_code || ',' ||
g_csv_output(i).ems_name || ',' ||
g_csv_output(i).item_no || ',"' ||
g_csv_output(i).lv_desc1 || '",' ||
g_csv_output(i).op || ',' ||
g_csv_output(i).primary_item || ',"' ||
g_csv_output(i).lv_desc2 || '",' ||
g_csv_output(i).replace_falg || ',' ||
g_csv_output(i).parent_item || ',' ||
g_csv_output(i).required_quantity || ',' ||
g_csv_output(i).issued_quantity || ',' ||
g_csv_output(i).open_quantity || ',' ||
g_csv_output(i).on_hand_quantity || ',' ||
g_csv_output(i).need_quantity || ',';
v_inv := varchar2_varray(); --create empty varray
v_inv.extend(25);
v_inv := g_csv_output(i).inv;
v_inv_names := '';
if inv_names.count > 0 then
for ix in 1 .. inv_names.count loop
if (inv_names(ix) is null or inv_names(ix) = '') then exit;
end if;
v_inv_names := v_inv_names || nvl(v_inv(ix),' ') || ',';
end loop;
end if;
w_output_string := w_output_string || ',' || v_inv_names;
fnd_file.put_line(2, w_output_string);
end if;
<>
null;
end loop; -- FOR i IN 1 .. g_csv_output.COUNT LOOP
<>
null;
exception
when e_program_err then
raise e_program_err;
when others then
raise;
end proc_csv_output;
type inv_array is table of varchar2(50) index by binary_integer;
inv_names varchar2_varray;
type tbl_csv_output is table of grec_csv_output index by binary_integer;
g_csv_output tbl_csv_output;
type grec_csv_output is record(
job_no varchar2(200),
lv_class_code varchar2(200),
ems_name varchar2(200),
item_no varchar2(200),
lv_desc1 varchar2(200),
op number,
replace_falg varchar2(1),
parent_item varchar2(200),
primary_item varchar2(200),
lv_desc2 varchar2(200),
required_quantity varchar2(200),
issued_quantity varchar2(200),
open_quantity varchar2(200),
on_hand_quantity varchar2(200),
need_quantity varchar2(200),
replace_quantity varchar2(200),
-- inv NUMBER_VARRAY25,
inv varchar2_varray,
po varchar2(200),
isactive varchar2(1));
CREATE SYNONYM varchar2_varray
FOR VARCHAR2_VARRAY
/
-- Start of DDL Script for Type SYSTEM.VARCHAR2_VARRAY
-- Generated 19-Nov-2012 10:54:58 from SYSTEM@EBS_8005
CREATE OR REPLACE
TYPE varchar2_varray AS VARRAY(10000) OF VARCHAR2(240);
/
-- Grants for Type
GRANT EXECUTE ON varchar2_varray TO apps
WITH GRANT OPTION
/
GRANT DEBUG ON varchar2_varray TO apps
WITH GRANT OPTION
/
procedure proc_csv_output is
w_output_string long;
v_inv varchar2_varray;
w_title varchar2(10000);
inv_name varchar2(4000);
lv_desc1 varchar2(240);
lv_desc2 varchar2(240);
lv_class_code wip_discrete_jobs.class_code%type;
v_inv_names varchar2(4000);
v_isactive varchar2(1);
l_str varchar2(30);
begin
w_title := 'WO_Number,Class Code,EMS ,Item No,Description,OP,PrimaryItem,Description'
||',Subs Flag,ParentItem,Required,Issued,OpenQty,EMS_Onhand,WO_Shortage,Subs Qty,';
v_inv_names := '';
if inv_names.count > 0 then
for ix in 1 .. inv_names.count loop
if (inv_names(ix) is null or inv_names(ix) = '') then exit; end if;
v_inv_names := v_inv_names || inv_names(ix) || ',';
end loop;
end if;
dbms_output.put_line('g_csv_output.COUNT='||g_csv_output.count);
fnd_file.put_line(fnd_file.log,g_csv_output.count);
w_title := w_title || v_inv_names || 'PackMtl.PO';
dbms_output.put_line('w_title='||w_title);
fnd_file.put_line(2, w_title);
v_isactive := 'Y';
for i in 1 .. g_csv_output.count loop
if g_csv_output(i).isactive is not null then
v_isactive := g_csv_output(i).isactive;
end if;
if g_item is not null and g_item != '%' then
l_str := g_item||'%';
if g_csv_output(i).item_no not like l_str then
goto xx_next_record;
end if;
end if;
if (g_csv_output(i).job_no is not null and v_isactive='Y') then
w_output_string :=
g_csv_output(i).job_no || ',' ||
g_csv_output(i).lv_class_code || ',' ||
g_csv_output(i).ems_name || ',' ||
g_csv_output(i).item_no || ',"' ||
g_csv_output(i).lv_desc1 || '",' ||
g_csv_output(i).op || ',' ||
g_csv_output(i).primary_item || ',"' ||
g_csv_output(i).lv_desc2 || '",' ||
g_csv_output(i).replace_falg || ',' ||
g_csv_output(i).parent_item || ',' ||
g_csv_output(i).required_quantity || ',' ||
g_csv_output(i).issued_quantity || ',' ||
g_csv_output(i).open_quantity || ',' ||
g_csv_output(i).on_hand_quantity || ',' ||
g_csv_output(i).need_quantity || ',';
v_inv := varchar2_varray(); --create empty varray
v_inv.extend(25);
v_inv := g_csv_output(i).inv;
v_inv_names := '';
if inv_names.count > 0 then
for ix in 1 .. inv_names.count loop
if (inv_names(ix) is null or inv_names(ix) = '') then exit;
end if;
v_inv_names := v_inv_names || nvl(v_inv(ix),' ') || ',';
end loop;
end if;
w_output_string := w_output_string || ',' || v_inv_names;
fnd_file.put_line(2, w_output_string);
end if;
<
null;
end loop; -- FOR i IN 1 .. g_csv_output.COUNT LOOP
<
null;
exception
when e_program_err then
raise e_program_err;
when others then
raise;
end proc_csv_output;
type inv_array is table of varchar2(50) index by binary_integer;
inv_names varchar2_varray;
type tbl_csv_output is table of grec_csv_output index by binary_integer;
g_csv_output tbl_csv_output;
type grec_csv_output is record(
job_no varchar2(200),
lv_class_code varchar2(200),
ems_name varchar2(200),
item_no varchar2(200),
lv_desc1 varchar2(200),
op number,
replace_falg varchar2(1),
parent_item varchar2(200),
primary_item varchar2(200),
lv_desc2 varchar2(200),
required_quantity varchar2(200),
issued_quantity varchar2(200),
open_quantity varchar2(200),
on_hand_quantity varchar2(200),
need_quantity varchar2(200),
replace_quantity varchar2(200),
-- inv NUMBER_VARRAY25,
inv varchar2_varray,
po varchar2(200),
isactive varchar2(1));
CREATE SYNONYM varchar2_varray
FOR VARCHAR2_VARRAY
/
-- Start of DDL Script for Type SYSTEM.VARCHAR2_VARRAY
-- Generated 19-Nov-2012 10:54:58 from SYSTEM@EBS_8005
CREATE OR REPLACE
TYPE varchar2_varray AS VARRAY(10000) OF VARCHAR2(240);
/
-- Grants for Type
GRANT EXECUTE ON varchar2_varray TO apps
WITH GRANT OPTION
/
GRANT DEBUG ON varchar2_varray TO apps
WITH GRANT OPTION
/
xxapsp0058
要將 Oracle EBS MRP 改寫
Organization_ID,Inventory_Item_ID,
CurrWeek,...1stWeek,...11Week,...21Week,...26Week
如果有 oe_odr_lines_sn snapshot
有 promise_date / schedule_ship_date
TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW') promise_date,
FLOOR(
(
TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW')
)/7) weeks,
PACKAGE BODY xxapsp0058_pkg
IS
/*************************************************************************************
NAME: XXAPSP0057_PKG. GET_WHEREUSE
PURPOSE: 1. XXAPSP0057 ON HANDS
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2012/11/11 Albert 1. Created this Package.
*************************************************************************************/
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
-- P_BU VARCHAR2 , --remove this paramenter form user request(once for all)
-- P_EMS VARCHAR2 , --(same with above)
-- P_PRODUCT_LINE VARCHAR2 , --(same with above)
p_oh VARCHAR2, --'Y/N'
p_so VARCHAR2, --'Y/N'
p_wip VARCHAR2, --'Y/N'
p_po VARCHAR2, --'Y/N'
p_mds VARCHAR2 --'Y/N'
)
IS
CURSOR c_so
IS
SELECT /*+PARALLEL */
TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW') promise_date,
FLOOR(
(TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW'))
/7) weeks,
sn.inventory_item_id, sn.organization_id,
-- a.segment1 AS inventory_item,A
-- mp.organization_code, -- mtl_parameters.organization_code=mtl_secondary_inventories.attribute10
-- oh.order_number||'-'||ol.line_number||'.'|| shipment_number AS order_number,
SUM(NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0)) AS quantity
FROM oe_odr_lines_sn sn
INNER JOIN oe_order_lines_all ol ON ol.line_id = sn.line_id
AND ol.flow_status_code = 'AWAITING_SHIPPING'
AND TRUNC (ol.schedule_ship_date) < TRUNC (SYSDATE, 'WW') -- + 6
INNER JOIN oe_order_headers_all oh ON ol.header_id = oh.header_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id = sn.organization_id
AND msi.secondary_inventory_name = ol.subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10 --EMS mtl_secondary_inventories
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = sn.inventory_item_id
AND mic.organization_id = sn.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
------------------------------------------------
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --sn.organization_id
--AND e.attribute12 = 'Open' -- this not a index key
--AND e.attribute10 IN (2,5) -- (same with above)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id --sn.organization_id
AND b.inventory_item_id = sn.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON a.organization_id =b.organization_id --a.segment1 AS inventory_item
AND a.inventory_item_id =b.inventory_item_id
AND a.wip_supply_type <> 6
WHERE NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0) > 0
AND FLOOR((TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW')) /7) < 26
GROUP BY
TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW'),
FLOOR(
(TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW'))
/7),
sn.inventory_item_id, sn.organization_id;
-- GROUP BY TRUNC(sn.promise_date+1,'WW'), -- promise_date,
-- sn.inventory_item_id,
-- mp.organization_id,
-- a.segment1; -- AS inventory_item,
-- MDS --
CURSOR c_mds
IS
SELECT /*+PARALLEL */
msd.inventory_item_id,
msd.organization_id,
a.segment1 AS inventory_item,
msd.schedule_designator AS order_number,
msd.schedule_quantity --schedule_quantity
FROM mrp_schedule_dates msd
INNER JOIN mtl_parameters mp ON mp.organization_id = msd.organization_id
-- AND mp.ORGANIZATION_CODE = NVL(P_EMS,mp.ORGANIZATION_CODE )
-- AND mp.ATTRIBUTE13 = NVL(P_BU, mp.ATTRIBUTE13)
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = msd.inventory_item_id
AND mic.organization_id = msd.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
-- AND mc.SEGMENT2 = NVL(P_PRODUCT_LINE, mc.SEGMENT
------------
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
--msd.organization_id --1,183,923
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
--msd.organization_id
AND b.inventory_item_id = msd.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE msd.schedule_designator LIKE '%PMALLC';
-- Work Order Order Scrap --
-- MSC_SUPPLIES 3 Work order
-- MSC_DEMANDS 17 Work Order scrap
CURSOR c_wo
IS
SELECT /*+PARALLEL */
we.wip_entity_name order_number, a.segment1 inventory_item,
mp.organization_id, wdj.primary_item_id inventory_item_id,
NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) wip_qty,
--CEIL (wdj.net_quantity * NVL (msb.shrinkage_rate, 0)) wip_scrap_qty
LEAST(CEIL (NVL(wdj.net_quantity,0) * NVL (msb.shrinkage_rate, 0)),NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) ) as wip_scrap_qty
FROM wip_dscr_jobs_sn wip
INNER JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id = wip.wip_entity_id
AND wdj.status_type IN (1, 3, 6)
INNER JOIN wip_entities we ON we.wip_entity_id = wip.wip_entity_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id = wdj.organization_id
AND msi.secondary_inventory_name = wdj.completion_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_system_items_b msb ON msb.organization_id = mp.organization_id
AND msb.inventory_item_id = wdj.primary_item_id
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = wdj.primary_item_id
AND mic.organization_id = wdj.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
AND b.inventory_item_id = wdj.primary_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
--AND NVL (wdj.start_quantity, 0) - NVL (wdj.quantity_completed, 0) > 0;
AND NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) > 0;
-- Work Order Demand --Component -- [Wip ???[status ??1. 3. 6 ???????H} OK
CURSOR c_wo_demand
IS
SELECT /*+PARALLEL */
wwo.inventory_item_id,
a.segment1 AS inventory_item,
mp.organization_id, -- wwo.ORGANIZATION_ID,
we.wip_entity_name AS order_number,
NVL (wwo.required_quantity, 0)
- NVL (wwo.quantity_issued, 0) AS wo_demand_qty
FROM wip_wreq_oprs_sn wwo
INNER JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id=wwo.wip_entity_id
AND wdj.status_type IN (1, 3, 6) --UNRELEASED, RELEASED, ON HOLD
INNER JOIN wip_entities we ON we.wip_entity_id = wwo.wip_entity_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id =wdj.organization_id
AND msi.secondary_inventory_name=wdj.completion_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = wwo.inventory_item_id
AND mic.organization_id = wwo.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --wwo.organization_id
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id --wwo.organization_id
AND b.inventory_item_id = wwo.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE NVL (wwo.required_quantity, 0) - NVL (wwo.quantity_issued, 0) <> 0;
-- MSC_SUPPLIES 1 Purchase order
-- MSC_SUPPLIES 2 Purchase requisition
-- MSC_SUPPLIES 8 PO in receiving
CURSOR c_po
IS
SELECT /*+PARALLEL */
COALESCE (po.segment1,
ro.segment1,
so.shipment_num
)
|| '-'
|| COALESCE (pl.line_num, rl.line_num, sl.line_num)
AS order_number,
CASE
WHEN po.segment1 IS NOT NULL
THEN 1
WHEN ro.segment1 IS NOT NULL
THEN 2
WHEN so.shipment_num IS NOT NULL
THEN 8
ELSE 0
END AS order_type,
sn.to_subinventory, msi.secondary_inventory_name,
mp.organization_id, sn.item_id AS inventory_item_id,
a.segment1 AS inventory_item, sn.supply_type_code, sn.quantity
FROM mtl_supply_sn sn
LEFT OUTER JOIN po_headers_all po ON sn.po_header_id =po.po_header_id
LEFT OUTER JOIN po_lines_all pl ON sn.po_line_id =pl.po_line_id
LEFT OUTER JOIN po_requisition_headers_all ro ON sn.req_header_id =ro.requisition_header_id
LEFT OUTER JOIN po_requisition_lines_all rl ON sn.req_line_id =rl.requisition_line_id
LEFT OUTER JOIN rcv_shipment_headers so ON sn.shipment_header_id=so.shipment_header_id
LEFT OUTER JOIN rcv_shipment_lines sl ON sn.shipment_line_id =sl.shipment_line_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id=
COALESCE (po.org_id, ro.org_id, so.organization_id)
AND msi.secondary_inventory_name=sn.to_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = sn.item_id
AND mic.organization_id = COALESCE (po.org_id, ro.org_id, so.organization_id)
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
--COALESCE (po.org_id, ro.org_id, so.organization_id)
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
--COALESCE (po.org_id, ro.org_id, so.organization_id)
AND b.inventory_item_id = sn.item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
-- AND MC.SEGMENT2 = NVL(P_PRODUCT_LINE, MC.SEGMENT2)
INNER JOIN cux.xx_c_mcatp_rule_t zz ON zz.inventory_item_id = sn.item_id
AND zz.organization_id = COALESCE (po.org_id, ro.org_id, so.organization_id)
WHERE 1=1;
--ON HwAND
CURSOR c_onhand
IS
SELECT /*+PARALLEL */
mp.attribute1 AS ems_group,
mp.organization_code AS ems,
miq.inventory_item_id,
mp.organization_id,
a.segment1 AS inventory_item,
msi.secondary_inventory_name AS subinventory_code,
SUM (miq.transaction_quantity) AS on_hand_quantity
FROM mtl_oh_qtys_sn miq
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id=miq.organization_id
AND msi.secondary_inventory_name=miq.subinventory_code
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
-- AND mp.ORGANIZATION_CODE = NVL(P_EMS , mp.ORGANIZATION_CODE)
-- AND mp.ATTRIBUTE13 = NVL(P_BU , mp.ATTRIBUTE13)
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = miq.inventory_item_id
AND mic.organization_id = miq.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
-- AND MC.SEGMENT2 = NVL(P_PRODUCT_LINE, MC.SEGMENT2)
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --1,183,923
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
AND b.inventory_item_id = miq.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id
--a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE 1 = 1
GROUP BY msi.secondary_inventory_name,
mp.organization_id,
mp.attribute1, -- EMS_GROUP
mp.organization_code, -- EMS
miq.inventory_item_id,
a.segment1
ORDER BY msi.secondary_inventory_name,
mp.organization_id,
mp.attribute1,
mp.organization_code,
miq.inventory_item_id,
a.segment1 ;
v_user_id NUMBER := fnd_global.user_id;
v_login_id NUMBER := fnd_global.conc_login_id;
v_bu_id NUMBER;
v_assembly_item_id NUMBER;
v_cnt NUMBER;
v_mod NUMBER := 0;
v_end_item_id NUMBER;
v_organization_id NUMBER;
v_component_item_id NUMBER;
v_counttable NUMBER;
v_char VARCHAR2 (10);
v_sql VARCHAR2 (2000);
v_string VARCHAR2 (2000);
v_plan_id NUMBER := 0;
v_instance_code VARCHAR2 (10) := 'EBS';
BEGIN
/*
v_mod := 0;
FOR r1 IN c_mds
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_mds v_mod='||v_mod);
--MSC_DEMANDS 3 Work order demand
v_mod := 0;
FOR r1 IN c_wo_demand
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_wo_demand v_mod='||v_mod);
-- MSC_SUPPLIES 3 Work order
-- MSC_DEMANDS 17 Work Order scrap
v_mod := 0;
FOR r1 IN c_wo
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_wo v_mod='||v_mod);
-- MSC_SUPPLIES 1 Purchase order
-- MSC_SUPPLIES 2 Purchase requisition
-- MSC_SUPPLIES 8 PO in receiving
v_mod := 0;
FOR r1 IN c_po
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_po v_mod='||v_mod);
*/
-- MSC_DEMANDS 30 Sales Orders
-- DBMS_OUTPUT.PUT_LINE('FOR R1 IN C10 LOOP');
v_mod := 0;
FOR r1 IN c_so
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_so v_mod='||v_mod);
/*
-- MSC_SUPPLIES 18 On Hand
v_mod := 0;
FOR r1 IN c_onhand
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_so v_mod='||v_mod);
*/
COMMIT;
<>
DBMS_OUTPUT.put_line ('....END....');
END main;
FUNCTION get_whereuse (p_component_item_id NUMBER, p_organization_id NUMBER)
RETURN NUMBER
IS
v_return NUMBER := 0;
v_count NUMBER := 0;
CURSOR c4 (x_component_item_id NUMBER, x_organization_id NUMBER)
IS
--SELECT * FROM (
SELECT bbo.assembly_item_id
-- , bic.component_item_id, bsc.substitute_component_id
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
-- bom_substitute_components bsc
WHERE bic.component_item_id = x_component_item_id
-- AND bsc.component_sequence_id = bic.component_sequence_id
-- AND bsc.substitute_component_id = x_component_item_id
AND bbo.organization_id = x_organization_id
ORDER BY bbo.assembly_item_id;
--) WHERE ROWNUM = 1;
CURSOR c4s (x_component_item_id NUMBER, x_organization_id NUMBER)
IS
-- SELECT * FROM (
SELECT bbo.assembly_item_id
-- , bic.component_item_id, bsc.substitute_component_id
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
INNER JOIN bom_substitute_components bsc ON bsc.component_sequence_id = bic.component_sequence_id
WHERE 1 = 1
AND bsc.substitute_component_id = x_component_item_id
AND bbo.organization_id = x_organization_id
ORDER BY bbo.assembly_item_id;
-- ) WHERE ROWNUM = 1;
v_component_item_id NUMBER := 0;
v_organization_id NUMBER := 0;
v_assembly_item_id NUMBER := 0;
BEGIN
v_assembly_item_id := p_component_item_id;
-- ?? cmponent ???X assembly
FOR r4 IN c4 (p_component_item_id, p_organization_id)
LOOP
v_assembly_item_id := r4.assembly_item_id;
SELECT COUNT (*)
INTO v_count
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
WHERE 1 = 1
AND bic.component_item_id = v_assembly_item_id
AND bbo.organization_id = p_organization_id;
IF v_count > 0
THEN
v_assembly_item_id := get_whereuse (v_assembly_item_id, p_organization_id);
END IF;
IF v_assembly_item_id <> p_component_item_id THEN
GOTO substitute;
END IF;
END LOOP;
<>
IF v_assembly_item_id = p_component_item_id
THEN --
FOR r4 IN c4s (p_component_item_id, p_organization_id)
LOOP
v_assembly_item_id := r4.assembly_item_id;
SELECT COUNT (*)
INTO v_count
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
WHERE bic.component_item_id = v_assembly_item_id
AND bbo.organization_id = p_organization_id;
IF v_count > 0
THEN
v_assembly_item_id := get_whereuse(v_assembly_item_id, p_organization_id);
END IF;
IF v_assembly_item_id <> p_component_item_id THEN
GOTO end_substitute;
END IF;
END LOOP;
END IF;
<>
-- FND_FILE.PUT_LINE(FND_FILE.OUTPUT , 'LOOP-4: ' || 'Y' ) ;
RETURN v_assembly_item_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_assembly_item_id;
END get_whereuse;
END XXAPSP0058_PKG;
Organization_ID,Inventory_Item_ID,
CurrWeek,...1stWeek,...11Week,...21Week,...26Week
如果有 oe_odr_lines_sn snapshot
有 promise_date / schedule_ship_date
TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW') promise_date,
FLOOR(
(
TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW')
)/7) weeks,
PACKAGE BODY xxapsp0058_pkg
IS
/*************************************************************************************
NAME: XXAPSP0057_PKG. GET_WHEREUSE
PURPOSE: 1. XXAPSP0057 ON HANDS
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2012/11/11 Albert 1. Created this Package.
*************************************************************************************/
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
-- P_BU VARCHAR2 , --remove this paramenter form user request(once for all)
-- P_EMS VARCHAR2 , --(same with above)
-- P_PRODUCT_LINE VARCHAR2 , --(same with above)
p_oh VARCHAR2, --'Y/N'
p_so VARCHAR2, --'Y/N'
p_wip VARCHAR2, --'Y/N'
p_po VARCHAR2, --'Y/N'
p_mds VARCHAR2 --'Y/N'
)
IS
CURSOR c_so
IS
SELECT /*+PARALLEL */
TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW') promise_date,
FLOOR(
(TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW'))
/7) weeks,
sn.inventory_item_id, sn.organization_id,
-- a.segment1 AS inventory_item,A
-- mp.organization_code, -- mtl_parameters.organization_code=mtl_secondary_inventories.attribute10
-- oh.order_number||'-'||ol.line_number||'.'|| shipment_number AS order_number,
SUM(NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0)) AS quantity
FROM oe_odr_lines_sn sn
INNER JOIN oe_order_lines_all ol ON ol.line_id = sn.line_id
AND ol.flow_status_code = 'AWAITING_SHIPPING'
AND TRUNC (ol.schedule_ship_date) < TRUNC (SYSDATE, 'WW') -- + 6
INNER JOIN oe_order_headers_all oh ON ol.header_id = oh.header_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id = sn.organization_id
AND msi.secondary_inventory_name = ol.subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10 --EMS mtl_secondary_inventories
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = sn.inventory_item_id
AND mic.organization_id = sn.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
------------------------------------------------
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --sn.organization_id
--AND e.attribute12 = 'Open' -- this not a index key
--AND e.attribute10 IN (2,5) -- (same with above)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id --sn.organization_id
AND b.inventory_item_id = sn.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON a.organization_id =b.organization_id --a.segment1 AS inventory_item
AND a.inventory_item_id =b.inventory_item_id
AND a.wip_supply_type <> 6
WHERE NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0) > 0
AND FLOOR((TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW')) /7) < 26
GROUP BY
TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW'),
FLOOR(
(TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW'))
/7),
sn.inventory_item_id, sn.organization_id;
-- GROUP BY TRUNC(sn.promise_date+1,'WW'), -- promise_date,
-- sn.inventory_item_id,
-- mp.organization_id,
-- a.segment1; -- AS inventory_item,
-- MDS --
CURSOR c_mds
IS
SELECT /*+PARALLEL */
msd.inventory_item_id,
msd.organization_id,
a.segment1 AS inventory_item,
msd.schedule_designator AS order_number,
msd.schedule_quantity --schedule_quantity
FROM mrp_schedule_dates msd
INNER JOIN mtl_parameters mp ON mp.organization_id = msd.organization_id
-- AND mp.ORGANIZATION_CODE = NVL(P_EMS,mp.ORGANIZATION_CODE )
-- AND mp.ATTRIBUTE13 = NVL(P_BU, mp.ATTRIBUTE13)
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = msd.inventory_item_id
AND mic.organization_id = msd.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
-- AND mc.SEGMENT2 = NVL(P_PRODUCT_LINE, mc.SEGMENT
------------
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
--msd.organization_id --1,183,923
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
--msd.organization_id
AND b.inventory_item_id = msd.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE msd.schedule_designator LIKE '%PMALLC';
-- Work Order Order Scrap --
-- MSC_SUPPLIES 3 Work order
-- MSC_DEMANDS 17 Work Order scrap
CURSOR c_wo
IS
SELECT /*+PARALLEL */
we.wip_entity_name order_number, a.segment1 inventory_item,
mp.organization_id, wdj.primary_item_id inventory_item_id,
NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) wip_qty,
--CEIL (wdj.net_quantity * NVL (msb.shrinkage_rate, 0)) wip_scrap_qty
LEAST(CEIL (NVL(wdj.net_quantity,0) * NVL (msb.shrinkage_rate, 0)),NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) ) as wip_scrap_qty
FROM wip_dscr_jobs_sn wip
INNER JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id = wip.wip_entity_id
AND wdj.status_type IN (1, 3, 6)
INNER JOIN wip_entities we ON we.wip_entity_id = wip.wip_entity_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id = wdj.organization_id
AND msi.secondary_inventory_name = wdj.completion_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_system_items_b msb ON msb.organization_id = mp.organization_id
AND msb.inventory_item_id = wdj.primary_item_id
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = wdj.primary_item_id
AND mic.organization_id = wdj.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
AND b.inventory_item_id = wdj.primary_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
--AND NVL (wdj.start_quantity, 0) - NVL (wdj.quantity_completed, 0) > 0;
AND NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) > 0;
-- Work Order Demand --Component -- [Wip ???[status ??1. 3. 6 ???????H} OK
CURSOR c_wo_demand
IS
SELECT /*+PARALLEL */
wwo.inventory_item_id,
a.segment1 AS inventory_item,
mp.organization_id, -- wwo.ORGANIZATION_ID,
we.wip_entity_name AS order_number,
NVL (wwo.required_quantity, 0)
- NVL (wwo.quantity_issued, 0) AS wo_demand_qty
FROM wip_wreq_oprs_sn wwo
INNER JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id=wwo.wip_entity_id
AND wdj.status_type IN (1, 3, 6) --UNRELEASED, RELEASED, ON HOLD
INNER JOIN wip_entities we ON we.wip_entity_id = wwo.wip_entity_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id =wdj.organization_id
AND msi.secondary_inventory_name=wdj.completion_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = wwo.inventory_item_id
AND mic.organization_id = wwo.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --wwo.organization_id
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id --wwo.organization_id
AND b.inventory_item_id = wwo.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE NVL (wwo.required_quantity, 0) - NVL (wwo.quantity_issued, 0) <> 0;
-- MSC_SUPPLIES 1 Purchase order
-- MSC_SUPPLIES 2 Purchase requisition
-- MSC_SUPPLIES 8 PO in receiving
CURSOR c_po
IS
SELECT /*+PARALLEL */
COALESCE (po.segment1,
ro.segment1,
so.shipment_num
)
|| '-'
|| COALESCE (pl.line_num, rl.line_num, sl.line_num)
AS order_number,
CASE
WHEN po.segment1 IS NOT NULL
THEN 1
WHEN ro.segment1 IS NOT NULL
THEN 2
WHEN so.shipment_num IS NOT NULL
THEN 8
ELSE 0
END AS order_type,
sn.to_subinventory, msi.secondary_inventory_name,
mp.organization_id, sn.item_id AS inventory_item_id,
a.segment1 AS inventory_item, sn.supply_type_code, sn.quantity
FROM mtl_supply_sn sn
LEFT OUTER JOIN po_headers_all po ON sn.po_header_id =po.po_header_id
LEFT OUTER JOIN po_lines_all pl ON sn.po_line_id =pl.po_line_id
LEFT OUTER JOIN po_requisition_headers_all ro ON sn.req_header_id =ro.requisition_header_id
LEFT OUTER JOIN po_requisition_lines_all rl ON sn.req_line_id =rl.requisition_line_id
LEFT OUTER JOIN rcv_shipment_headers so ON sn.shipment_header_id=so.shipment_header_id
LEFT OUTER JOIN rcv_shipment_lines sl ON sn.shipment_line_id =sl.shipment_line_id
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id=
COALESCE (po.org_id, ro.org_id, so.organization_id)
AND msi.secondary_inventory_name=sn.to_subinventory
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = sn.item_id
AND mic.organization_id = COALESCE (po.org_id, ro.org_id, so.organization_id)
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id
--COALESCE (po.org_id, ro.org_id, so.organization_id)
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
--COALESCE (po.org_id, ro.org_id, so.organization_id)
AND b.inventory_item_id = sn.item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id --a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
-- AND MC.SEGMENT2 = NVL(P_PRODUCT_LINE, MC.SEGMENT2)
INNER JOIN cux.xx_c_mcatp_rule_t zz ON zz.inventory_item_id = sn.item_id
AND zz.organization_id = COALESCE (po.org_id, ro.org_id, so.organization_id)
WHERE 1=1;
--ON HwAND
CURSOR c_onhand
IS
SELECT /*+PARALLEL */
mp.attribute1 AS ems_group,
mp.organization_code AS ems,
miq.inventory_item_id,
mp.organization_id,
a.segment1 AS inventory_item,
msi.secondary_inventory_name AS subinventory_code,
SUM (miq.transaction_quantity) AS on_hand_quantity
FROM mtl_oh_qtys_sn miq
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id=miq.organization_id
AND msi.secondary_inventory_name=miq.subinventory_code
AND msi.availability_type = 1
INNER JOIN mtl_parameters mp ON mp.organization_code = msi.attribute10
-- AND mp.ORGANIZATION_CODE = NVL(P_EMS , mp.ORGANIZATION_CODE)
-- AND mp.ATTRIBUTE13 = NVL(P_BU , mp.ATTRIBUTE13)
INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = miq.inventory_item_id
AND mic.organization_id = miq.organization_id
INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
AND mc.segment1 IN ('FG', 'SM')
-- AND MC.SEGMENT2 = NVL(P_PRODUCT_LINE, MC.SEGMENT2)
INNER JOIN mtl_parameters e ON e.organization_id = mp.organization_id --1,183,923
--AND e.attribute12 = 'Open'
--AND e.attribute10 IN (2,5)
INNER JOIN mtl_item_categories b ON b.organization_id = mp.organization_id
AND b.inventory_item_id = miq.inventory_item_id
AND b.category_set_id = 1
INNER JOIN mtl_categories_b c ON c.category_id = b.category_id
INNER JOIN mtl_system_items_b a ON b.organization_id = a.organization_id
--a.segment1 AS inventory_item,
AND b.inventory_item_id = a.inventory_item_id
AND a.wip_supply_type <> 6 --
WHERE 1 = 1
GROUP BY msi.secondary_inventory_name,
mp.organization_id,
mp.attribute1, -- EMS_GROUP
mp.organization_code, -- EMS
miq.inventory_item_id,
a.segment1
ORDER BY msi.secondary_inventory_name,
mp.organization_id,
mp.attribute1,
mp.organization_code,
miq.inventory_item_id,
a.segment1 ;
v_user_id NUMBER := fnd_global.user_id;
v_login_id NUMBER := fnd_global.conc_login_id;
v_bu_id NUMBER;
v_assembly_item_id NUMBER;
v_cnt NUMBER;
v_mod NUMBER := 0;
v_end_item_id NUMBER;
v_organization_id NUMBER;
v_component_item_id NUMBER;
v_counttable NUMBER;
v_char VARCHAR2 (10);
v_sql VARCHAR2 (2000);
v_string VARCHAR2 (2000);
v_plan_id NUMBER := 0;
v_instance_code VARCHAR2 (10) := 'EBS';
BEGIN
/*
v_mod := 0;
FOR r1 IN c_mds
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_mds v_mod='||v_mod);
--MSC_DEMANDS 3 Work order demand
v_mod := 0;
FOR r1 IN c_wo_demand
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_wo_demand v_mod='||v_mod);
-- MSC_SUPPLIES 3 Work order
-- MSC_DEMANDS 17 Work Order scrap
v_mod := 0;
FOR r1 IN c_wo
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_wo v_mod='||v_mod);
-- MSC_SUPPLIES 1 Purchase order
-- MSC_SUPPLIES 2 Purchase requisition
-- MSC_SUPPLIES 8 PO in receiving
v_mod := 0;
FOR r1 IN c_po
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_po v_mod='||v_mod);
*/
-- MSC_DEMANDS 30 Sales Orders
-- DBMS_OUTPUT.PUT_LINE('FOR R1 IN C10 LOOP');
v_mod := 0;
FOR r1 IN c_so
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_so v_mod='||v_mod);
/*
-- MSC_SUPPLIES 18 On Hand
v_mod := 0;
FOR r1 IN c_onhand
LOOP
v_mod := v_mod + 1;
END LOOP;
DBMS_OUTPUT.put_line ( 'c_so v_mod='||v_mod);
*/
COMMIT;
<
DBMS_OUTPUT.put_line ('....END....');
END main;
FUNCTION get_whereuse (p_component_item_id NUMBER, p_organization_id NUMBER)
RETURN NUMBER
IS
v_return NUMBER := 0;
v_count NUMBER := 0;
CURSOR c4 (x_component_item_id NUMBER, x_organization_id NUMBER)
IS
--SELECT * FROM (
SELECT bbo.assembly_item_id
-- , bic.component_item_id, bsc.substitute_component_id
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
-- bom_substitute_components bsc
WHERE bic.component_item_id = x_component_item_id
-- AND bsc.component_sequence_id = bic.component_sequence_id
-- AND bsc.substitute_component_id = x_component_item_id
AND bbo.organization_id = x_organization_id
ORDER BY bbo.assembly_item_id;
--) WHERE ROWNUM = 1;
CURSOR c4s (x_component_item_id NUMBER, x_organization_id NUMBER)
IS
-- SELECT * FROM (
SELECT bbo.assembly_item_id
-- , bic.component_item_id, bsc.substitute_component_id
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
INNER JOIN bom_substitute_components bsc ON bsc.component_sequence_id = bic.component_sequence_id
WHERE 1 = 1
AND bsc.substitute_component_id = x_component_item_id
AND bbo.organization_id = x_organization_id
ORDER BY bbo.assembly_item_id;
-- ) WHERE ROWNUM = 1;
v_component_item_id NUMBER := 0;
v_organization_id NUMBER := 0;
v_assembly_item_id NUMBER := 0;
BEGIN
v_assembly_item_id := p_component_item_id;
-- ?? cmponent ???X assembly
FOR r4 IN c4 (p_component_item_id, p_organization_id)
LOOP
v_assembly_item_id := r4.assembly_item_id;
SELECT COUNT (*)
INTO v_count
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
WHERE 1 = 1
AND bic.component_item_id = v_assembly_item_id
AND bbo.organization_id = p_organization_id;
IF v_count > 0
THEN
v_assembly_item_id := get_whereuse (v_assembly_item_id, p_organization_id);
END IF;
IF v_assembly_item_id <> p_component_item_id THEN
GOTO substitute;
END IF;
END LOOP;
<
IF v_assembly_item_id = p_component_item_id
THEN --
FOR r4 IN c4s (p_component_item_id, p_organization_id)
LOOP
v_assembly_item_id := r4.assembly_item_id;
SELECT COUNT (*)
INTO v_count
FROM bom_structures_b bbo
INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
WHERE bic.component_item_id = v_assembly_item_id
AND bbo.organization_id = p_organization_id;
IF v_count > 0
THEN
v_assembly_item_id := get_whereuse(v_assembly_item_id, p_organization_id);
END IF;
IF v_assembly_item_id <> p_component_item_id THEN
GOTO end_substitute;
END IF;
END LOOP;
END IF;
<
-- FND_FILE.PUT_LINE(FND_FILE.OUTPUT , 'LOOP-4: ' || 'Y' ) ;
RETURN v_assembly_item_id;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_assembly_item_id;
END get_whereuse;
END XXAPSP0058_PKG;
訂閱：
意見 (Atom)
