[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(SUM(DECODE(p_upd_ship,'Y',
DECODE(PH.STATUS_CODE,'PENDING',0,'OPEN',0, PLD.SHIPPED_QUANTITY), PLD.SHIPPED_QUANTITY)), 0) SHIPPED_TD,
NVL(SUM(DECODE(D.ASN_SEQ_NUMBER,l_asn_num,0, DECODE(p_upd_ship,'Y', DECODE(PH.STATUS_CODE,'PENDING',0,'OPEN',0, PLD. SHIPPED_QUANTITY), PLD.SHIPPED_QUANTITY))), 0) ALREADY_SHIPPED
FROM SO_PICKING_HEADERS_ALL PH
, SO_PICKING_LINES_ALL PL2
, SO_PICKING_LINES_ALL PL1
, SO_PICKING_LINE_DETAILS PLD
, WSH_DELIVERIES D
WHERE PH.PICKING_HEADER_ID = PL2.PICKING_HEADER_ID
AND PL2.PICKING_LINE_ID = PLD.PICKING_LINE_ID
AND PL1.PICKING_LINE_ID = l_pl_id
AND PL1.ORDER_LINE_ID = PL2.ORDER_LINE_ID
AND D.DELIVERY_ID = PLD.DELIVERY_ID
AND DECODE(l_asn_num,NULL,-1,D.ASN_SEQ_NUMBER) <= nvl(l_asn_num,-1);
l_sql_statement := 'SELECT PLD.DEPARTURE_ID'||
', PLD.DELIVERY_ID'||
', D.ASN_SEQ_NUMBER'||
', PL.PICKING_LINE_ID'||
', PL.ORDER_LINE_ID'||
', PL.COMPONENT_CODE'||
', PL.COMPONENT_RATIO'||
', PL.COMPONENT_SEQUENCE_ID'||
', PL.UNIT_CODE'||
', PL.WAREHOUSE_ID'||
', PL.INVENTORY_ITEM_ID'||
', PL.CUSTOMER_ITEM_ID'||
', PL.SHIP_TO_CONTACT_ID'||
', NVL(SUM(DECODE(:p_upd_ship,''Y'','||
' DECODE(PH.STATUS_CODE,''PENDING'',0,''OPEN'',0, PLD.SHIPPED_QUANTITY),'||
' PLD.SHIPPED_QUANTITY)),'||
' 0)'||
'FROM WSH_DELIVERIES D'||
', SO_PICKING_HEADERS_ALL PH'||
', SO_PICKING_LINES_ALL PL'||
', SO_PICKING_LINE_DETAILS PLD '||
'WHERE PH.PICKING_HEADER_ID = PL.PICKING_HEADER_ID '||
'AND PL.PICKING_LINE_ID = PLD.PICKING_LINE_ID '||
'AND PLD.DELIVERY_ID = D.DELIVERY_ID ';
INSERT INTO WSH_REPORT_TEMP
( REPORT_TEMP_ID
, DEPARTURE_ID
, DELIVERY_ID
, SHIPPED_FLAG
, LINE_ID
, ITEM_INDENTATION
, COMPONENT_CODE
, COMPONENT_RATIO
, COMPONENT_SEQUENCE_ID
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, CUSTOMER_ITEM_ID
, SHIP_TO_CONTACT_ID
, SHIPPED_QUANTITY
, TOTAL_SHIPPED_TODATE
, TOTAL_ALREADY_SHIPPED
, QUANTITY_TO_INVOICE
, UNIT_OF_MEASURE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY)
VALUES
( p_report_id
, l_f_dep_id
, l_f_del_id
, 'Y'
, l_f_line_id
, NVL(LENGTH(TRANSLATE(l_f_comp_code,'X1234567890','X')),0)+1
, l_f_comp_code
, l_f_comp_ratio
, l_f_comp_seq_id
, l_f_warehouse_id
, l_f_item_id
, l_f_cust_item_id
, l_f_ship_to_contact_id
, l_f_shipped_qty
, l_shipped_td
, l_already_shipped
, l_f_shipped_qty
, l_f_unit_code
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID);
INSERT_ORDER_LINE(p_report_id, l_f_dep_id, l_f_del_id,l_f_line_id,p_mode);
UPDATE WSH_REPORT_TEMP R
SET
( R.CONFIGURATION_ITEM_FLAG
, R.REQUIRED_FOR_REVENUE_FLAG
, R.COMPONENT_RATIO
, R.ORDERED_QUANTITY
, R.SELLING_PRICE
, R.ORDER_NUMBER
, R.PURCHASE_ORDER_NUM
, R.CURRENCY_CODE ) =
( SELECT MAX(LD.CONFIGURATION_ITEM_FLAG)
, MAX(LD.REQUIRED_FOR_REVENUE_FLAG)
, DECODE(R.COMPONENT_RATIO,'',MAX(LD.COMPONENT_RATIO),
R.COMPONENT_RATIO)
, L.ORDERED_QUANTITY * NVL(R.COMPONENT_RATIO,1)
, L.SELLING_PRICE
, H.ORDER_NUMBER
, H.PURCHASE_ORDER_NUM
, H.CURRENCY_CODE
FROM SO_LINE_DETAILS LD
, SO_HEADERS_ALL H
, SO_LINES_ALL L
WHERE H.HEADER_ID = L.HEADER_ID
AND L.LINE_ID = LD.LINE_ID
AND LD.LINE_ID = R.LINE_ID
AND LD.INVENTORY_ITEM_ID+0 = R.INVENTORY_ITEM_ID
GROUP BY L.ORDERED_QUANTITY * NVL(R.COMPONENT_RATIO,1)
, L.SELLING_PRICE
, H.ORDER_NUMBER
, H.PURCHASE_ORDER_NUM
, H.CURRENCY_CODE )
, R.INCLUDE_ON_SHIP_DOCS =
( SELECT BOM.INCLUDE_ON_SHIP_DOCS
FROM BOM_INVENTORY_COMPONENTS BOM
WHERE BOM.COMPONENT_SEQUENCE_ID = R.COMPONENT_SEQUENCE_ID
AND p_mode IN ('PAK','INV'))
WHERE R.REPORT_TEMP_ID = p_report_id
AND R.DEPARTURE_ID = l_dep_id;
UPDATE WSH_REPORT_TEMP R
SET R.INCLUDE_ON_SHIP_DOCS = 0
WHERE R.REPORT_TEMP_ID = p_report_id
AND R.DEPARTURE_ID = l_dep_id
AND (R.CONFIGURATION_ITEM_FLAG = 'Y'
OR
EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS M
WHERE M.INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID
AND M.ORGANIZATION_ID = R.ORGANIZATION_ID
AND M.ITEM_TYPE IN ('AOC','POC','OC')));
UPDATE WSH_REPORT_TEMP R
SET R.ORDERED_QUANTITY = NULL,
R.SHIPPED_QUANTITY = NULL,
R.TOTAL_ALREADY_SHIPPED = NULL,
R.TOTAL_SHIPPED_TODATE = NULL,
R.QUANTITY_TO_INVOICE = NULL,
R.UNIT_OF_MEASURE = NULL
WHERE R.REPORT_TEMP_ID = p_report_id
AND R.DEPARTURE_ID = l_dep_id
AND R.CONFIGURATION_ITEM_FLAG = 'Y';
UPDATE WSH_REPORT_TEMP
SET INCLUDE_ON_SHIP_DOCS = 0
WHERE REPORT_TEMP_ID = p_report_id
AND DEPARTURE_ID = l_dep_id
AND CONFIGURATION_ITEM_FLAG = 'Y';
FOR DREC IN (SELECT * FROM WSH_REPORT_TEMP
WHERE REPORT_TEMP_ID = p_report_id) LOOP
--dbms_output.enable(1000000);
SELECT DISTINCT RT.DEPARTURE_ID
, RT.DELIVERY_ID
, LD.LINE_ID
, LD.COMPONENT_CODE
, LD.COMPONENT_RATIO
, LD.COMPONENT_SEQUENCE_ID
, NVL(LENGTH(TRANSLATE(LD.COMPONENT_CODE,'X1234567890','X')),0)+1
ITEM_INDENTATION -- strips all numerics and counts hyphen
, LD.WAREHOUSE_ID
, LD.INVENTORY_ITEM_ID
, LD.CUSTOMER_ITEM_ID
, LD.CONFIGURATION_ITEM_FLAG
, LD.REQUIRED_FOR_REVENUE_FLAG
, LD.UNIT_CODE
FROM SO_LINE_DETAILS LD
, WSH_REPORT_TEMP RT
WHERE LD.LINE_ID = RT.LINE_ID
AND LD.SHIPPABLE_FLAG = 'N'
AND RT.REPORT_TEMP_ID = l_rep_id
-- make sure the non shipable line detail is not an order line.
AND NOT EXISTS (SELECT 'ORDERED LINE'
FROM SO_LINES_ALL L
WHERE L.LINE_ID = LD.LINE_ID
AND L.COMPONENT_CODE = LD.COMPONENT_CODE);
/**** always select non shippable lines irrespective if any component lines where pick releases
and exists (select 'shipped component line'
from so_picking_lines_all
where order_line_id in (select line_id from wsh_report_temp where report_temp_id= rep_id)
and component_code != ld.component_code
and component_code like ld.component_code||'%')
***/
BEGIN
IF p_debug = 'ON' THEN
--dbms_output.enable(1000000);
INSERT INTO WSH_REPORT_TEMP
( REPORT_TEMP_ID
, DEPARTURE_ID
, DELIVERY_ID
, SHIPPED_FLAG
, LINE_ID
, ITEM_INDENTATION
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, CUSTOMER_ITEM_ID
, CONFIGURATION_ITEM_FLAG
, REQUIRED_FOR_REVENUE_FLAG
, COMPONENT_CODE
, COMPONENT_RATIO
, COMPONENT_SEQUENCE_ID
, UNIT_OF_MEASURE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY)
VALUES
( p_report_id
, NSLINE.departure_id
, NSLINE.delivery_id
, 'N'
, NSLINE.line_id
, NSLINE.item_indentation
, NSLINE.warehouse_id
, NSLINE.inventory_item_id
, NSLINE.customer_item_id
, NSLINE.configuration_item_flag
, NSLINE.required_for_revenue_flag
, NSLINE.component_code
, NSLINE.component_ratio
, NSLINE.component_sequence_id
, NSLINE.unit_code
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID);
SELECT MAX(ITEM_INDENTATION)
FROM WSH_REPORT_TEMP
WHERE REPORT_TEMP_ID = l_rep_id;
UPDATE WSH_REPORT_TEMP R
SET ( R.TOTAL_SHIPPED_TODATE
, R.TOTAL_ALREADY_SHIPPED
, R.SHIPPED_QUANTITY
, R.SHIP_TO_CONTACT_ID ) =
( SELECT MIN(S.TOTAL_SHIPPED_TODATE/S.COMPONENT_RATIO)
, MIN(S.TOTAL_ALREADY_SHIPPED/S.COMPONENT_RATIO)
, MIN(S.TOTAL_SHIPPED_TODATE/S.COMPONENT_RATIO) -
MIN(S.TOTAL_ALREADY_SHIPPED/S.COMPONENT_RATIO)
, MIN(S.SHIP_TO_CONTACT_ID)
FROM WSH_REPORT_TEMP S
WHERE S.REPORT_TEMP_ID = R.REPORT_TEMP_ID
AND S.DELIVERY_ID = R.DELIVERY_ID
AND S.ITEM_INDENTATION = R.ITEM_INDENTATION + 1
AND S.COMPONENT_CODE LIKE R. COMPONENT_CODE||'%')
WHERE R.ITEM_INDENTATION = l_i
AND R.REPORT_TEMP_ID = p_report_id
AND R.SHIPPED_QUANTITY IS NULL;
SELECT MAX(ITEM_INDENTATION)-1
FROM WSH_REPORT_TEMP
WHERE REPORT_TEMP_ID = l_rep_id;
UPDATE WSH_REPORT_TEMP R
SET ( R.QUANTITY_TO_INVOICE, R.CHILD_RFR_FLAG) =
( SELECT MIN (S.TOTAL_SHIPPED_TODATE/S.COMPONENT_RATIO) -
MIN (S.TOTAL_ALREADY_SHIPPED/S.COMPONENT_RATIO)
, 'Y'
FROM WSH_REPORT_TEMP S
WHERE S.REPORT_TEMP_ID = R.REPORT_TEMP_ID
AND S.DELIVERY_ID = R.DELIVERY_ID
AND S.ITEM_INDENTATION = R.ITEM_INDENTATION + 1
AND (S.REQUIRED_FOR_REVENUE_FLAG = 'Y'
OR
S.CHILD_RFR_FLAG = 'Y')
AND S.COMPONENT_CODE LIKE R.COMPONENT_CODE||'%')
WHERE R.ITEM_INDENTATION = l_i
AND R.QUANTITY_TO_INVOICE IS NULL
AND R.REPORT_TEMP_ID = p_report_id;
UPDATE WSH_REPORT_TEMP R
SET ( R.QUANTITY_TO_INVOICE, R.CHILD_RFR_FLAG) =
( SELECT MIN (S.TOTAL_SHIPPED_TODATE/S.COMPONENT_RATIO) -
MIN (S.TOTAL_ALREADY_SHIPPED/S.COMPONENT_RATIO)
, 'N'
FROM WSH_REPORT_TEMP S
WHERE S.REPORT_TEMP_ID = R.REPORT_TEMP_ID
AND S.DELIVERY_ID = R.DELIVERY_ID
AND S.ITEM_INDENTATION = R.ITEM_INDENTATION + 1
AND S.COMPONENT_CODE LIKE R.COMPONENT_CODE||'%')
WHERE R.ITEM_INDENTATION = l_i
AND R.REPORT_TEMP_ID = p_report_id
AND R.QUANTITY_TO_INVOICE IS NULL;
PROCEDURE INSERT_ORDER_LINE (p_report_id IN NUMBER,
p_departure_id IN NUMBER,
p_delivery_id IN NUMBER,
p_line_id IN NUMBER,
p_mode IN VARCHAR2 DEFAULT NULL,
p_debug IN VARCHAR2 DEFAULT 'OFF') IS
BEGIN
DECLARE
CURSOR ORDER_LINES (l_rep_id IN NUMBER,
l_del_id IN NUMBER,
l_id IN NUMBER) IS
SELECT L.LINK_TO_LINE_ID
, L.COMPONENT_CODE
, L.COMPONENT_SEQUENCE_ID
, L.WAREHOUSE_ID
, L.INVENTORY_ITEM_ID
, L.CUSTOMER_ITEM_ID
, L.ORDERED_QUANTITY
, L.SELLING_PRICE
, L.UNIT_CODE
, NVL(L.ATO_FLAG,'N')
FROM SO_LINES_ALL L
WHERE L.LINE_ID = l_id
AND L.LINE_TYPE_CODE IN ('DETAIL','REGULAR')
AND NOT EXISTS (SELECT 'ALREADY EXISTS IN TEMP TABLE'
FROM WSH_REPORT_TEMP T
WHERE T.REPORT_TEMP_ID = l_rep_id
AND T.LINE_ID = L.LINE_ID
AND T.DELIVERY_ID = l_del_id
AND T.SHIPPED_FLAG = 'N');
INSERT INTO WSH_REPORT_TEMP
( REPORT_TEMP_ID
, DEPARTURE_ID
, DELIVERY_ID
, SHIPPED_FLAG
, LINE_ID
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, ITEM_INDENTATION
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, CUSTOMER_ITEM_ID
, ORDERED_QUANTITY
, SELLING_PRICE
, UNIT_OF_MEASURE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY)
SELECT p_report_id
, p_departure_id
, p_delivery_id
, 'N'
, p_line_id
, l_component_code
, l_component_sequence_id
, NVL(LENGTH(TRANSLATE(l_component_code,'X1234567890','X')),0)+1
, l_warehouse_id
, l_item_id
, l_customer_item_id
, l_ordered_quantity
, l_selling_price
, l_unit_code
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
FROM DUAL
WHERE NOT EXISTS
(SELECT 'ALREADY EXISTS IN TEMP TABLE'
FROM WSH_REPORT_TEMP
WHERE REPORT_TEMP_ID = p_report_id
AND LINE_ID = p_line_id
AND DELIVERY_ID = p_delivery_id
AND INVENTORY_ITEM_ID = l_item_id);
INSERT_ATO_COMPONENTS(p_report_id, p_departure_id, p_delivery_id, p_line_id,p_mode);
INSERT_ORDER_LINE (p_report_id, p_departure_id, p_delivery_id, l_link_line,p_mode);
FND_MESSAGE.Set_Token('PACKAGE','WSH_REPORT_QUANTITIES.insert_order_line');
END INSERT_ORDER_LINE;
PROCEDURE INSERT_ATO_COMPONENTS (p_report_id IN NUMBER,
p_departure_id IN NUMBER,
p_delivery_id IN NUMBER,
p_line_id IN NUMBER,
p_mode IN VARCHAR2 DEFAULT NULL,
p_debug IN VARCHAR2 DEFAULT 'OFF') IS
BEGIN
DECLARE
CURSOR ORDER_LINES (l_rep_id IN NUMBER,
l_del_id IN NUMBER,
l_id IN NUMBER) IS
SELECT L.LINK_TO_LINE_ID
, L.COMPONENT_CODE
, L.COMPONENT_SEQUENCE_ID
, L.WAREHOUSE_ID
, L.INVENTORY_ITEM_ID
, L.CUSTOMER_ITEM_ID
, L.ORDERED_QUANTITY
, L.SELLING_PRICE
, L.UNIT_CODE
, L.LINE_ID
FROM SO_LINES_ALL L
WHERE L.ATO_LINE_ID = l_id
AND L.LINE_TYPE_CODE IN ('DETAIL','REGULAR')
AND NOT EXISTS (SELECT 'ALREADY EXISTS IN TEMP TABLE'
FROM WSH_REPORT_TEMP T
WHERE T.REPORT_TEMP_ID = l_rep_id
AND T.LINE_ID = L.LINE_ID
AND T.DELIVERY_ID = l_del_id
AND T.SHIPPED_FLAG = 'N');
INSERT INTO WSH_REPORT_TEMP
( REPORT_TEMP_ID
, DEPARTURE_ID
, DELIVERY_ID
, SHIPPED_FLAG
, LINE_ID
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, ITEM_INDENTATION
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, CUSTOMER_ITEM_ID
, ORDERED_QUANTITY
, SELLING_PRICE
, UNIT_OF_MEASURE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY)
SELECT p_report_id
, p_departure_id
, p_delivery_id
, 'N'
, l_line_id
, l_component_code
, l_component_sequence_id
, NVL(LENGTH(TRANSLATE(l_component_code,'X1234567890','X')),0)+1
, l_warehouse_id
, l_item_id
, l_customer_item_id
, l_ordered_quantity
, l_selling_price
, l_unit_code
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
FROM DUAL
WHERE NOT EXISTS
(SELECT 'ALREADY EXISTS IN TEMP TABLE'
FROM WSH_REPORT_TEMP
WHERE REPORT_TEMP_ID = p_report_id
AND LINE_ID = l_line_id
AND DELIVERY_ID = p_delivery_id
AND INVENTORY_ITEM_ID = l_item_id);
INSERT_ORDER_LINE (p_report_id, p_departure_id, p_delivery_id, l_link_line, p_mode);
FND_MESSAGE.Set_Token('PACKAGE','WSH_REPORT_QUANTITIES.insert_ato_components');
END INSERT_ATO_COMPONENTS;
SELECT SUM(SHIPPED_QUANTITY)
FROM WSH_REPORT_TEMP
WHERE REPORT_TEMP_ID = l_rep_id
AND LINE_ID = l_ord_line_id
AND INVENTORY_ITEM_ID = l_item_id;
PROCEDURE DELETE_REPORT (p_report_id IN NUMBER) IS
BEGIN
DELETE FROM WSH_REPORT_TEMP
WHERE REPORT_TEMP_ID = p_report_id
OR CREATION_DATE < sysdate - 2;
END DELETE_REPORT;