[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT INDEX_NUM2 DRAFT_ID,
INDEX_NUM1 LINELOCATION_ID
FROM PO_SESSION_GT
WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID';
SELECT GT.LINE_LOCATION_ID, Nvl(MAX(POD.DISTRIBUTION_NUM),0) MAX_DIST_NUM
FROM PO_DISTRIBUTIONS_MERGE_V POD,
PO_DISTRIBUTIONS_GT GT
WHERE GT.LINE_LOCATION_ID = POD.LINE_LOCATION_ID(+)
GROUP BY GT.LINE_LOCATION_ID;
SELECT INDEX_NUM2, NUM1 INTO L_DRAFT_ID, L_HEADER_ID
FROM PO_SESSION_GT
WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID' AND ROWNUM < 2;
SELECT PH.CURRENCY_CODE, PH.RATE, GLSOB.CURRENCY_CODE, GLSOB.SET_OF_BOOKS_ID, PH.ORG_ID
INTO L_CURRENCY_CODE, L_RATE , L_FUNC_CURRENCY_CODE, L_SOB, L_ORG_ID
FROM PO_HEADERS_DRAFT_ALL PH,
GL_SETS_OF_BOOKS GLSOB,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE PH.PO_HEADER_ID = L_HEADER_ID
AND PH.DRAFT_ID = L_DRAFT_ID
AND GLSOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND OOD.ORGANIZATION_ID = PH.ORG_ID AND ROWNUM < 2;
SELECT PH.CURRENCY_CODE, PH.RATE, GLSOB.CURRENCY_CODE, GLSOB.SET_OF_BOOKS_ID, PH.ORG_ID
INTO L_CURRENCY_CODE, L_RATE , L_FUNC_CURRENCY_CODE, L_SOB, L_ORG_ID
FROM PO_HEADERS_ALL PH,
GL_SETS_OF_BOOKS GLSOB,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE PH.PO_HEADER_ID = L_HEADER_ID
AND GLSOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND OOD.ORGANIZATION_ID = PH.ORG_ID AND ROWNUM < 2;
SELECT EXPENSE_ACCRUAL_CODE
INTO L_EXPENSE_ACCRUAL_CD
FROM PO_SYSTEM_PARAMETERS PSP
WHERE ORG_ID = L_ORG_ID
AND ROWNUM < 2 ;
UPDATE PO_SESSION_GT GT
SET NUM1 = (SELECT FUNDS_TO_USE FROM TABLE(P_LINK_REQ_DIST_FU_TBL) TBL
WHERE TBL.DISTRIBUTION_ID = GT.INDEX_NUM1 AND GT.INDEX_CHAR1 = 'LINKREQDIST_REQDISTID')
WHERE GT.INDEX_CHAR1 = 'LINKREQDIST_REQDISTID';
DEBUG(D_MODULE, D_POSITION, 'SESSION GT UPDATED WITH FUNDS TO USE.');
DEBUG(D_MODULE, D_POSITION, 'INSERTING INTO PO_DISTRIBUTIONS_GT TABLE WITH DEFAULT VALUES FROM REQ');
INSERT INTO PO_DISTRIBUTIONS_GT --
(PO_DISTRIBUTION_ID,
DRAFT_ID,
--WHO COLUMNS
---------------------------------------------------------------
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
---------------------------------------------------------------
PO_HEADER_ID,
PO_LINE_ID,
LINE_LOCATION_ID,
PO_RELEASE_ID,
REQ_DISTRIBUTION_ID,
SET_OF_BOOKS_ID,
CODE_COMBINATION_ID,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_PERSON_ID,
QUANTITY_ORDERED,
QUANTITY_DELIVERED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
AMOUNT_ORDERED,
AMOUNT_DELIVERED,
AMOUNT_CANCELLED,
AMOUNT_BILLED,
RATE_DATE,
RATE,
ACCRUED_FLAG,
ENCUMBERED_FLAG,
GL_ENCUMBERED_PERIOD_NAME,
GL_ENCUMBERED_DATE,
DISTRIBUTION_NUM,
DESTINATION_TYPE_CODE,
DESTINATION_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,
BUDGET_ACCOUNT_ID,
ACCRUAL_ACCOUNT_ID,
VARIANCE_ACCOUNT_ID,
DEST_CHARGE_ACCOUNT_ID,
DEST_VARIANCE_ACCOUNT_ID,
WIP_ENTITY_ID,
WIP_LINE_ID,
WIP_REPETITIVE_SCHEDULE_ID,
WIP_OPERATION_SEQ_NUM,
WIP_RESOURCE_SEQ_NUM,
BOM_RESOURCE_ID,
PREVENT_ENCUMBRANCE_FLAG,
PROJECT_ID,
TASK_ID,
END_ITEM_UNIT_NUMBER,
EXPENDITURE_TYPE,
PROJECT_ACCOUNTING_CONTEXT,
DESTINATION_CONTEXT,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ITEM_DATE,
ACCRUE_ON_RECEIPT_FLAG,
KANBAN_CARD_ID,
TAX_RECOVERY_OVERRIDE_FLAG,
RECOVERY_RATE,
AWARD_ID,
OKE_CONTRACT_LINE_ID,
OKE_CONTRACT_DELIVERABLE_ID,
ORG_ID,
DISTRIBUTION_TYPE,
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED ,
GROUP_LINE_ID,
CLM_MISC_LOA,
CLM_DEFENCE_FUNDING,
CLM_FMS_CASE_NUMBER,
CLM_AGENCY_ACCT_IDENTIFIER,
PAR_DRAFT_ID, -- FOR LINKING PAR
PAR_DISTRIBUTION_ID -- FOR LINKING PAR DISTRIBUTION
)
SELECT PO_DISTRIBUTIONS_S.NEXTVAL,
POH.DRAFT_ID,
--DEFAULT WHO COLUMNS
---------------------------------------------------------------
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
SYSDATE,
---------------------------------------------------------------
PLL.PO_HEADER_ID,
POL.PO_LINE_ID,
PLL.LINE_LOCATION_ID,
PLL.PO_RELEASE_ID,
PRD.DISTRIBUTION_ID,
NVL(L_SOB, PRD.SET_OF_BOOKS_ID),
PRD.CODE_COMBINATION_ID,
PRL.DELIVER_TO_LOCATION_ID,
DECODE(PLL.DROP_SHIP_FLAG,'Y',NULL,PRL.TO_PERSON_ID),
NULL, -- QUANTITY ORDERED
0,
0,
0,
Round(((L_DERIVED_TFU * (DAR_REF.DAR/ P_TOTAL_DIST_AMT_REQUIRED)) * (P_TBL.FUNDS_TO_USE/ L_TOTAL_FUNDS_TO_USE)), X_PRECISION) DIST_AMT,
0, -- AMOUNT_DELIVERED
0, -- AMOUNT_CANCELLED
0, -- AMOUNT_BILLED
POH.RATE_DATE,
POH.RATE,
DECODE((NVL(PRL.PCARD_FLAG,'N')) ,'N','N','Y'), --X_ACCRUED_FLAG,
'N',--ENCUMBERED_FLAG
-- if the profile option po_autocreate_date is set to req gl date
-- use req's gl date to build encum gl date, otherwise use sysdate.
GET_GL_PERIOD_NAME,
GET_GL_DATE(
DECODE( FND_PROFILE.VALUE('PO_AUTOCREATE_DATE')
, 'REQ GL DATE', PRD.GL_ENCUMBERED_DATE
, TRUNC(SYSDATE) ), L_SOB),
--L_PERIOD_NAME,
-1, --PDI.DISTRIBUTION_NUM,
PRL.DESTINATION_TYPE_CODE,
PRL.DESTINATION_ORGANIZATION_ID,
PRL.DESTINATION_SUBINVENTORY,
PRD.BUDGET_ACCOUNT_ID,
PRD.ACCRUAL_ACCOUNT_ID,
PRD.VARIANCE_ACCOUNT_ID,
NULL, --PDI.DEST_CHARGE_ACCOUNT_ID,
NULL, --PDI.DEST_VARIANCE_ACCOUNT_ID,
PRL.WIP_ENTITY_ID,
PRL.WIP_LINE_ID,
PRL.WIP_REPETITIVE_SCHEDULE_ID,
PRL.WIP_OPERATION_SEQ_NUM,
PRL.WIP_RESOURCE_SEQ_NUM,
PRL.BOM_RESOURCE_ID,
'N', --PREVENT_ENCUMBRANCE_FLAG
PRD.PROJECT_ID,
PRD.TASK_ID,
PRD.END_ITEM_UNIT_NUMBER,
PRD.EXPENDITURE_TYPE,
PRD.PROJECT_ACCOUNTING_CONTEXT,
PRL.DESTINATION_CONTEXT,
PRD.EXPENDITURE_ORGANIZATION_ID,
PRD.EXPENDITURE_ITEM_DATE,
DECODE(RECEIPT_REQUIRED_FLAG, 'N','N',DECODE(L_EXPENSE_ACCRUAL_CD,'PERIOD END' ,'N','Y')), --ACCRUE_ON_RECEIPT_FLAG
PRL.KANBAN_CARD_ID,
PRD.TAX_RECOVERY_OVERRIDE_FLAG,
DECODE(PRD.TAX_RECOVERY_OVERRIDE_FLAG, 'Y', PRD.RECOVERY_RATE, NULL),
PRD.AWARD_ID,
PRD.OKE_CONTRACT_LINE_ID,
PRD.OKE_CONTRACT_DELIVERABLE_ID,
L_ORG_ID,
PLL.SHIPMENT_TYPE,
'N', --PRD.PARTIAL_FUNDED_FLAG,
NULL, -- FUNDED VALUE
NULL, -- QUANTITY FUNDED
Round(((L_DERIVED_TFU * ( DAR_REF.DAR/ P_TOTAL_DIST_AMT_REQUIRED)) * (P_TBL.FUNDS_TO_USE/ L_TOTAL_FUNDS_TO_USE)), X_PRECISION) AMOUNT_FUNDED,
NULL, --PDI.GROUP_LINE_ID,
PRD.CLM_MISC_LOA,
PRD.CLM_DEFENCE_FUNDING,
PRD.CLM_FMS_CASE_NUMBER,
PRD.CLM_AGENCY_ACCT_IDENTIFIER,
PRD.PAR_DRAFT_ID, -- FOR LINKING PAR
PRD.PAR_DISTRIBUTION_ID -- FOR LINKING PAR DISTRIBUTION
FROM
TABLE(P_LINK_REQ_DIST_FU_TBL) P_TBL,
PO_REQUISITION_LINES_ALL PRL,
PO_REQ_DISTRIBUTIONS_ALL PRD,
PO_LINE_LOCATIONS_MERGE_V PLL,
PO_LINES_MERGE_V POL ,
PO_HEADERS_DRAFT_ALL POH,
(
SELECT
POLL.LINE_LOCATION_ID ,
POLL.DRAFT_ID,
(
(DECODE(POLL.MATCHING_BASIS
, 'AMOUNT', POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0)
, ((POLL.QUANTITY-NVL(POLL.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE))
)
-
Nvl((SELECT SUM(DECODE(POLL.MATCHING_BASIS
, 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED,0)
, ((POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE)
)
)
FROM PO_DISTRIBUTIONS_MERGE_V POD
WHERE POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POLL.DRAFT_ID = POD.DRAFT_ID
AND Nvl(POD.DELETE_FLAG, 'N') = 'N'),0)
)DAR
FROM PO_LINE_LOCATIONS_MERGE_V POLL
WHERE EXISTS
(SELECT 1 FROM PO_SESSION_GT PGT
WHERE PGT.INDEX_CHAR1 = 'LINKREQDIST_LINELOCID'
AND PGT.INDEX_NUM2 = POLL.DRAFT_ID
AND PGT.INDEX_NUM1 = POLL.LINE_LOCATION_ID)
) DAR_REF
WHERE PRD.DISTRIBUTION_ID = P_TBL.DISTRIBUTION_ID AND
PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND
EXISTS (SELECT 1 FROM PO_SESSION_GT PGT
WHERE PGT.INDEX_CHAR1 = 'LINKREQDIST_LINELOCID'
AND PGT.INDEX_NUM2 = PLL.DRAFT_ID
AND PGT.INDEX_NUM1 = PLL.LINE_LOCATION_ID)
AND PLL.DRAFT_ID = POL.DRAFT_ID
AND PLL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.DRAFT_ID = POH.DRAFT_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND DAR_REF.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND DAR_REF.DRAFT_ID = PLL.DRAFT_ID ;
DEBUG(D_MODULE, D_POSITION, 'FOR MOD, INSERTED INTO PO_DISTRIBUTIONS_GT TABLE WITH DEFAULT VALUES FROM REQ');
INSERT INTO PO_DISTRIBUTIONS_GT --
(PO_DISTRIBUTION_ID,
DRAFT_ID,
--WHO COLUMNS
---------------------------------------------------------------
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
---------------------------------------------------------------
PO_HEADER_ID,
PO_LINE_ID,
LINE_LOCATION_ID,
PO_RELEASE_ID,
REQ_DISTRIBUTION_ID,
SET_OF_BOOKS_ID,
CODE_COMBINATION_ID,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_PERSON_ID,
QUANTITY_ORDERED,
QUANTITY_DELIVERED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
AMOUNT_ORDERED,
AMOUNT_DELIVERED,
AMOUNT_CANCELLED,
AMOUNT_BILLED,
RATE_DATE,
RATE,
ACCRUED_FLAG,
ENCUMBERED_FLAG,
GL_ENCUMBERED_PERIOD_NAME,
GL_ENCUMBERED_DATE,
DISTRIBUTION_NUM,
DESTINATION_TYPE_CODE,
DESTINATION_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,
BUDGET_ACCOUNT_ID,
ACCRUAL_ACCOUNT_ID,
VARIANCE_ACCOUNT_ID,
DEST_CHARGE_ACCOUNT_ID,
DEST_VARIANCE_ACCOUNT_ID,
WIP_ENTITY_ID,
WIP_LINE_ID,
WIP_REPETITIVE_SCHEDULE_ID,
WIP_OPERATION_SEQ_NUM,
WIP_RESOURCE_SEQ_NUM,
BOM_RESOURCE_ID,
PREVENT_ENCUMBRANCE_FLAG,
PROJECT_ID,
TASK_ID,
END_ITEM_UNIT_NUMBER,
EXPENDITURE_TYPE,
PROJECT_ACCOUNTING_CONTEXT,
DESTINATION_CONTEXT,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ITEM_DATE,
ACCRUE_ON_RECEIPT_FLAG,
KANBAN_CARD_ID,
TAX_RECOVERY_OVERRIDE_FLAG,
RECOVERY_RATE,
AWARD_ID,
OKE_CONTRACT_LINE_ID,
OKE_CONTRACT_DELIVERABLE_ID,
ORG_ID,
DISTRIBUTION_TYPE,
--PARTIAL FUNDING ATTRIBUTES
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED ,
GROUP_LINE_ID,
CLM_MISC_LOA,
CLM_DEFENCE_FUNDING,
CLM_FMS_CASE_NUMBER,
CLM_AGENCY_ACCT_IDENTIFIER
)
SELECT PO_DISTRIBUTIONS_S.NEXTVAL,
NVL(POH.DRAFT_ID,-1),
--DEFAULT WHO COLUMNS
---------------------------------------------------------------
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
SYSDATE,
---------------------------------------------------------------
PLL.PO_HEADER_ID,
POL.PO_LINE_ID,
PLL.LINE_LOCATION_ID,
PLL.PO_RELEASE_ID,
PRD.DISTRIBUTION_ID,
NVL(L_SOB, PRD.SET_OF_BOOKS_ID),
PRD.CODE_COMBINATION_ID,
PRL.DELIVER_TO_LOCATION_ID,
DECODE(PLL.DROP_SHIP_FLAG,'Y',NULL,PRL.TO_PERSON_ID),
NULL, -- QUANTITY ORDERED
0,
0,
0,
Round(((L_DERIVED_TFU * ( DAR_REF.DAR/ P_TOTAL_DIST_AMT_REQUIRED)) * (P_TBL.FUNDS_TO_USE/ L_TOTAL_FUNDS_TO_USE)), X_PRECISION) DIST_AMT,
0, -- AMOUNT_DELIVERED
0, -- AMOUNT_CANCELLED
0, -- AMOUNT_BILLED
POH.RATE_DATE,
POH.RATE,
DECODE( (NVL(PRL.PCARD_FLAG,'N')) ,'N','N','Y'), --X_ACCRUED_FLAG,
'N',--ENCUMBERED_FLAG
-- if the profile option po_autocreate_date is set to req gl date
-- use req's gl date to build encum gl date, otherwise use sysdate.
GET_GL_PERIOD_NAME,
GET_GL_DATE(
DECODE( FND_PROFILE.VALUE('PO_AUTOCREATE_DATE')
, 'REQ GL DATE', PRD.GL_ENCUMBERED_DATE
, TRUNC(SYSDATE) ), L_SOB),
--L_PERIOD_NAME,
-1, --PDI.DISTRIBUTION_NUM,
PRL.DESTINATION_TYPE_CODE,
PRL.DESTINATION_ORGANIZATION_ID,
PRL.DESTINATION_SUBINVENTORY,
PRD.BUDGET_ACCOUNT_ID,
PRD.ACCRUAL_ACCOUNT_ID,
PRD.VARIANCE_ACCOUNT_ID,
NULL, --PDI.DEST_CHARGE_ACCOUNT_ID,
NULL, --PDI.DEST_VARIANCE_ACCOUNT_ID,
PRL.WIP_ENTITY_ID,
PRL.WIP_LINE_ID,
PRL.WIP_REPETITIVE_SCHEDULE_ID,
PRL.WIP_OPERATION_SEQ_NUM,
PRL.WIP_RESOURCE_SEQ_NUM,
PRL.BOM_RESOURCE_ID,
'N', --PREVENT_ENCUMBRANCE_FLAG
PRD.PROJECT_ID,
PRD.TASK_ID,
PRD.END_ITEM_UNIT_NUMBER,
PRD.EXPENDITURE_TYPE,
PRD.PROJECT_ACCOUNTING_CONTEXT,
PRL.DESTINATION_CONTEXT,
PRD.EXPENDITURE_ORGANIZATION_ID,
PRD.EXPENDITURE_ITEM_DATE,
DECODE(RECEIPT_REQUIRED_FLAG, 'N','N',DECODE(L_EXPENSE_ACCRUAL_CD,'PERIOD END' ,'N','Y')), --ACCRUE_ON_RECEIPT_FLAG
PRL.KANBAN_CARD_ID,
PRD.TAX_RECOVERY_OVERRIDE_FLAG,
DECODE(PRD.TAX_RECOVERY_OVERRIDE_FLAG, 'Y', PRD.RECOVERY_RATE, NULL),
PRD.AWARD_ID,
PRD.OKE_CONTRACT_LINE_ID,
PRD.OKE_CONTRACT_DELIVERABLE_ID,
L_ORG_ID,
PLL.SHIPMENT_TYPE,
'N', --PRD.PARTIAL_FUNDED_FLAG,
NULL, -- FUNDED VALUE
NULL, -- QUANTITY FUNDED
Round(((L_DERIVED_TFU * ( DAR_REF.DAR/ P_TOTAL_DIST_AMT_REQUIRED)) * (P_TBL.FUNDS_TO_USE/ L_TOTAL_FUNDS_TO_USE)), X_PRECISION) AMOUNT_FUNDED,
NULL, --PDI.GROUP_LINE_ID,
PRD.CLM_MISC_LOA,
PRD.CLM_DEFENCE_FUNDING,
PRD.CLM_FMS_CASE_NUMBER,
PRD.CLM_AGENCY_ACCT_IDENTIFIER
FROM
TABLE(P_LINK_REQ_DIST_FU_TBL) P_TBL,
PO_REQUISITION_LINES_ALL PRL,
PO_REQ_DISTRIBUTIONS_ALL PRD,
PO_LINE_LOCATIONS_ALL PLL,
PO_LINES_ALL POL ,
PO_HEADERS_ALL POH,
(
SELECT
POLL.LINE_LOCATION_ID ,
POLL.DRAFT_ID,
(
(DECODE(POLL.MATCHING_BASIS
, 'AMOUNT', POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0)
, ((POLL.QUANTITY-NVL(POLL.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE))
)
-
Nvl((SELECT SUM(DECODE(POLL.MATCHING_BASIS
, 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED,0)
, ((POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE)
)
)
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID),0)
)DAR
FROM PO_LINE_LOCATIONS_ALL POLL
WHERE EXISTS
(SELECT 1 FROM PO_SESSION_GT PGT
WHERE PGT.INDEX_CHAR1 = 'LINKREQDIST_LINELOCID'
AND PGT.INDEX_NUM1 = POLL.LINE_LOCATION_ID)
) DAR_REF
WHERE PRD.DISTRIBUTION_ID = P_TBL.DISTRIBUTION_ID
AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND EXISTS (SELECT 1 FROM PO_SESSION_GT PGT
WHERE PGT.INDEX_CHAR1 = 'LINKREQDIST_LINELOCID'
AND PGT.INDEX_NUM1 = PLL.LINE_LOCATION_ID)
AND PLL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND DAR_REF.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID;
DEBUG(D_MODULE, D_POSITION, 'FOR BASE DOC, INSERTED INTO PO_DISTRIBUTIONS_GT TABLE WITH DEFAULT VALUES FROM REQ');
SELECT POLL.LINE_LOCATION_ID , Sum(AMOUNT_ORDERED) TOTAL_AMT, Max(PO_DISTRIBUTION_ID) DISTRIBUTION_ID,
Max(
(DECODE(POLL.MATCHING_BASIS
, 'AMOUNT', POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0)
, ((POLL.QUANTITY-NVL(POLL.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE))
)
-
Nvl((SELECT SUM(DECODE(POLL.MATCHING_BASIS
, 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED,0)
, ((POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE)
)
)
FROM PO_DISTRIBUTIONS_MERGE_V POD
WHERE POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POLL.DRAFT_ID = POD.DRAFT_ID
AND Nvl(POD.DELETE_FLAG, 'N') = 'N'),0)
)DAR
FROM PO_LINE_LOCATIONS_MERGE_V POLL, PO_DISTRIBUTIONS_GT GT
WHERE GT.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND GT.DRAFT_ID = POLL.DRAFT_ID
GROUP BY POLL.LINE_LOCATION_ID
) LOOP
UPDATE PO_DISTRIBUTIONS_GT T
SET T.AMOUNT_ORDERED = Round((L_DERIVED_TFU * CALIBRATE_SCH_CUR.DAR / P_TOTAL_DIST_AMT_REQUIRED),X_PRECISION) - (CALIBRATE_SCH_CUR.TOTAL_AMT - T.AMOUNT_ORDERED)
WHERE T.PO_DISTRIBUTION_ID = CALIBRATE_SCH_CUR.DISTRIBUTION_ID
AND T.LINE_LOCATION_ID = CALIBRATE_SCH_CUR.LINE_LOCATION_ID ;
DEBUG(D_MODULE, D_POSITION, ' Updated no. of rows: '|| sql%ROWCOUNT) ;
SELECT POLL.LINE_LOCATION_ID , Sum(AMOUNT_ORDERED) TOTAL_AMT, Max(PO_DISTRIBUTION_ID) DISTRIBUTION_ID,
Max(
(DECODE(POLL.MATCHING_BASIS
, 'AMOUNT', POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0)
, ((POLL.QUANTITY-NVL(POLL.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE))
)
-
Nvl((SELECT SUM(DECODE(POLL.MATCHING_BASIS
, 'AMOUNT', POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED,0)
, ((POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0))*POLL.PRICE_OVERRIDE)
)
)
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID),0)
) DAR
FROM PO_LINE_LOCATIONS_ALL POLL, PO_DISTRIBUTIONS_GT GT
WHERE GT.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
GROUP BY POLL.LINE_LOCATION_ID
) LOOP
UPDATE PO_DISTRIBUTIONS_GT T
SET T.AMOUNT_ORDERED = Round((L_DERIVED_TFU * CALIBRATE_SCH_CUR.DAR / P_TOTAL_DIST_AMT_REQUIRED),X_PRECISION) - (CALIBRATE_SCH_CUR.TOTAL_AMT - T.AMOUNT_ORDERED)
WHERE T.PO_DISTRIBUTION_ID = CALIBRATE_SCH_CUR.DISTRIBUTION_ID
AND T.LINE_LOCATION_ID = CALIBRATE_SCH_CUR.LINE_LOCATION_ID ;
DEBUG(D_MODULE, D_POSITION, ' Updated no. of rows: '|| sql%ROWCOUNT) ;
SELECT REQ_DISTRIBUTION_ID, Sum(AMOUNT_ORDERED) TOTAL_REQ_AMT, Max(P_TBL.FUNDS_TO_USE) FUNDS_TO_USE, Max(PO_DISTRIBUTION_ID) DISTRIBUTION_ID
FROM PO_DISTRIBUTIONS_GT GT,TABLE(P_LINK_REQ_DIST_FU_TBL) P_TBL
WHERE GT.REQ_DISTRIBUTION_ID = P_TBL.DISTRIBUTION_ID
GROUP BY REQ_DISTRIBUTION_ID
) LOOP
UPDATE PO_DISTRIBUTIONS_GT T
SET T.AMOUNT_ORDERED = CALIBRATE_REQDIST_CUR.FUNDS_TO_USE - (CALIBRATE_REQDIST_CUR.TOTAL_REQ_AMT - T.AMOUNT_ORDERED)
WHERE T.PO_DISTRIBUTION_ID = CALIBRATE_REQDIST_CUR.DISTRIBUTION_ID
AND T.REQ_DISTRIBUTION_ID = CALIBRATE_REQDIST_CUR.REQ_DISTRIBUTION_ID ;
DEBUG(D_MODULE, D_POSITION, ' Updated no. of rows: '|| sql%ROWCOUNT) ;
UPDATE PO_DISTRIBUTIONS_GT GT
SET QUANTITY_ORDERED = (SELECT DECODE(POL.MATCHING_BASIS, 'AMOUNT', NULL, Trunc((NVL(GT.AMOUNT_ORDERED,0) / POL.PRICE_OVERRIDE),Nvl(FND_PROFILE.VALUE('PO_QUANTITY_PRECISION'),5)) )
FROM PO_LINE_LOCATIONS_MERGE_V POL
WHERE GT.LINE_LOCATION_ID = POL.LINE_LOCATION_ID
AND GT.DRAFT_ID = POL.DRAFT_ID),
AMOUNT_ORDERED = (SELECT DECODE(POL.MATCHING_BASIS, 'AMOUNT', AMOUNT_ORDERED, NULL)
FROM PO_LINE_LOCATIONS_MERGE_V POL
WHERE GT.LINE_LOCATION_ID = POL.LINE_LOCATION_ID
AND GT.DRAFT_ID = POL.DRAFT_ID);
UPDATE PO_DISTRIBUTIONS_GT GT
SET FUNDED_VALUE = (SELECT DECODE(POL.MATCHING_BASIS, 'AMOUNT', Round(GT.AMOUNT_ORDERED * NVL(L_RATE,1), X_BASE_PRECISION), Round((QUANTITY_ORDERED * POL.PRICE_OVERRIDE * NVL(L_RATE,1)),X_BASE_PRECISION) )
FROM PO_LINE_LOCATIONS_MERGE_V POL
WHERE GT.LINE_LOCATION_ID = POL.LINE_LOCATION_ID
AND GT.DRAFT_ID = POL.DRAFT_ID);
UPDATE PO_DISTRIBUTIONS_GT GT
SET DISTRIBUTION_NUM = DIST_NUM_C.MAX_DIST_NUM + ROWNUM,
QUANTITY_FUNDED = QUANTITY_ORDERED,
AMOUNT_FUNDED = AMOUNT_ORDERED
WHERE GT.LINE_LOCATION_ID = DIST_NUM_C.LINE_LOCATION_ID;
SELECT REQ_DISTRIBUTION_ID,
SUM(NEW_DIST_GT.AMOUNT_ORDERED) TOTAL_AMOUNT_ORDERED,
SUM(NEW_DIST_GT.FUNDED_VALUE) TOTAL_FUNDED_VALUE,
MIN(REQ_DIST_GT.NUM1) FUNDS_TO_USE,
MIN(PO_REQS_INQ_SV.GET_REQ_AMOUNT ('J' , REQ_DISTRIBUTION_ID)) REQ_DIST_AMOUNT,
Min(DV.FUNDS_REMAINING) FUNDS_REMAINING
FROM PO_SESSION_GT REQ_DIST_GT,
PO_DISTRIBUTIONS_GT NEW_DIST_GT,
PO_CLMREQ_DIST_DETAILS_V DV
WHERE REQ_DIST_GT.INDEX_NUM1 = NEW_DIST_GT.REQ_DISTRIBUTION_ID
AND REQ_DIST_GT.INDEX_CHAR1 = 'LINKREQDIST_REQDISTID'
AND DV.distribution_id = NEW_DIST_GT.REQ_DISTRIBUTION_ID
GROUP BY NEW_DIST_GT.REQ_DISTRIBUTION_ID;
SELECT COUNT(DISTINCT (PRL.REQUISITION_LINE_ID))
INTO L_AT_SOURCING_REQS
FROM PO_DISTRIBUTIONS_GT GT,
PO_REQUISITION_LINES_ALL PRL,
PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE GT.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID
AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND NVL(PRL.AT_SOURCING_FLAG,'N') = 'Y';
SELECT INDEX_NUM2, NUM1 INTO L_DRAFT_ID, L_HEADER_ID
FROM PO_SESSION_GT
WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID' AND ROWNUM < 2;
SELECT PH.CURRENCY_CODE, PH.RATE, GLSOB.CURRENCY_CODE
INTO L_CURRENCY_CODE, L_RATE , L_FUNC_CURRENCY_CODE
FROM PO_HEADERS_DRAFT_ALL PH,
GL_SETS_OF_BOOKS GLSOB,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE PH.PO_HEADER_ID = L_HEADER_ID
AND PH.DRAFT_ID = L_DRAFT_ID
AND GLSOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND OOD.ORGANIZATION_ID = PH.ORG_ID AND ROWNUM < 2;
SELECT GT.FUNDED_VALUE,
DECODE(POL.MATCHING_BASIS, 'AMOUNT', (GT.AMOUNT_ORDERED * NVL(L_RATE,1)), Round(GT.QUANTITY_ORDERED * PLL.PRICE_OVERRIDE * NVL(L_RATE,1),X_BASE_PRECISION)) DIST_AMT,
Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
PLL.SHIPMENT_NUM,
GT.DISTRIBUTION_NUM
FROM PO_DISTRIBUTIONS_GT GT,
PO_LINE_LOCATIONS_MERGE_V PLL,
PO_LINES_MERGE_V POL
WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
GT.DRAFT_ID = PLL.DRAFT_ID AND
POL.PO_LINE_ID = PLL.PO_LINE_ID AND
PLL.DRAFT_ID = POL.DRAFT_ID
) LOOP
D_POSITION := 51;
SELECT PH.CURRENCY_CODE, PH.RATE, GLSOB.CURRENCY_CODE
INTO L_CURRENCY_CODE, L_RATE , L_FUNC_CURRENCY_CODE
FROM PO_HEADERS_ALL PH,
GL_SETS_OF_BOOKS GLSOB,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE PH.PO_HEADER_ID = L_HEADER_ID
AND GLSOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
AND OOD.ORGANIZATION_ID = PH.ORG_ID AND ROWNUM < 2;
SELECT GT.FUNDED_VALUE,
DECODE(POL.MATCHING_BASIS, 'AMOUNT', (GT.AMOUNT_ORDERED * NVL(L_RATE,1)), Round(GT.QUANTITY_ORDERED * PLL.PRICE_OVERRIDE * NVL(L_RATE,1),X_BASE_PRECISION)) DIST_AMT,
Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
PLL.SHIPMENT_NUM,
GT.DISTRIBUTION_NUM
FROM PO_DISTRIBUTIONS_GT GT,
PO_LINE_LOCATIONS_ALL PLL,
PO_LINES_ALL POL
WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
POL.PO_LINE_ID = PLL.PO_LINE_ID
) LOOP
D_POSITION := 51;
SELECT Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
PLL.SHIPMENT_NUM,
PLD.DISTRIBUTION_NUM
FROM
PO_DISTRIBUTIONS_GT GT,
PO_LINES_MERGE_V POL,
PO_LINE_LOCATIONS_MERGE_V PLL,
PO_DISTRIBUTIONS_MERGE_V PLD
WHERE GT.LINE_LOCATION_ID = PLD.LINE_LOCATION_ID AND
GT.DRAFT_ID = PLD.DRAFT_ID AND
PLD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
PLL.DRAFT_ID = PLD.DRAFT_ID AND
PLL.PO_LINE_ID = POL.PO_LINE_ID AND
PLL.DRAFT_ID = POL.DRAFT_ID AND
PLD.DISTRIBUTION_NUM = GT.DISTRIBUTION_NUM;
SELECT Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
PLL.SHIPMENT_NUM,
PLD.DISTRIBUTION_NUM
FROM
PO_DISTRIBUTIONS_GT GT,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL PLL,
PO_DISTRIBUTIONS_ALL PLD
WHERE GT.LINE_LOCATION_ID = PLD.LINE_LOCATION_ID AND
PLD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
PLL.PO_LINE_ID = POL.PO_LINE_ID AND
PLD.DISTRIBUTION_NUM = GT.DISTRIBUTION_NUM;
SELECT Min(Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM)) LINE_NUM,
PLL.SHIPMENT_NUM,
GT.DISTRIBUTION_NUM
FROM
PO_DISTRIBUTIONS_GT GT,
PO_LINES_MERGE_V POL,
PO_LINE_LOCATIONS_MERGE_V PLL
WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
GT.DRAFT_ID = PLL.DRAFT_ID AND
PLL.PO_LINE_ID = POL.PO_LINE_ID AND
PLL.DRAFT_ID = POL.DRAFT_ID
GROUP BY POL.LINE_NUM, PLL.SHIPMENT_NUM, GT.DISTRIBUTION_NUM
HAVING Count(1) > 1;
SELECT Min(Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM)) LINE_NUM,
PLL.SHIPMENT_NUM,
GT.DISTRIBUTION_NUM
FROM
PO_DISTRIBUTIONS_GT GT,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL PLL
WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
PLL.PO_LINE_ID = POL.PO_LINE_ID
GROUP BY POL.LINE_NUM, PLL.SHIPMENT_NUM, GT.DISTRIBUTION_NUM
HAVING Count(1) > 1;
SELECT Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
PLL.SHIPMENT_NUM,
GT.DISTRIBUTION_NUM,
GT.QUANTITY_ORDERED,
GT.AMOUNT_ORDERED,
PLL.MATCHING_BASIS
FROM
PO_DISTRIBUTIONS_GT GT,
PO_LINES_MERGE_V POL,
PO_LINE_LOCATIONS_MERGE_V PLL
WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
GT.DRAFT_ID = PLL.DRAFT_ID AND
PLL.PO_LINE_ID = POL.PO_LINE_ID AND
PLL.DRAFT_ID = POL.DRAFT_ID ;
SELECT Nvl(POL.LINE_NUM_DISPLAY,POL.LINE_NUM) LINE_NUM,
PLL.SHIPMENT_NUM,
GT.DISTRIBUTION_NUM,
GT.QUANTITY_ORDERED,
GT.AMOUNT_ORDERED,
PLL.MATCHING_BASIS
FROM
PO_DISTRIBUTIONS_GT GT,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL PLL
WHERE GT.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
PLL.PO_LINE_ID = POL.PO_LINE_ID ;
SELECT DISTINCT PO_DISTRIBUTION_ID, CODE_COMBINATION_ID, '',CLM_MISC_LOA,'NEW'
BULK COLLECT INTO L_DIST_ID_TBL, L_CHARGE_ACC_TBL, L_ACRN_TBL,
L_LOA_TBL,L_CHANGE_STAT_TBL
FROM ((SELECT PDD.PO_DISTRIBUTION_ID, PDD.CODE_COMBINATION_ID
,PDD.CLM_MISC_LOA
FROM PO_DISTRIBUTIONS_GT PDD
WHERE PDD.PO_HEADER_ID = L_HEADER_ID
AND PDD.DRAFT_ID = L_DRAFT_ID )
UNION ALL
(SELECT PLD.PO_DISTRIBUTION_ID, PLD.CODE_COMBINATION_ID,
PLD.CLM_MISC_LOA
FROM PO_DISTRIBUTIONS_MERGE_V PLD
WHERE PLD.PO_HEADER_ID = L_HEADER_ID
AND PLD.DRAFT_ID = L_DRAFT_ID)
)
ORDER BY PO_DISTRIBUTION_ID ;
UPDATE PO_DISTRIBUTIONS_DRAFT_ALL
SET ACRN = L_ACRN_TBL(J)
WHERE PO_DISTRIBUTION_ID = L_DIST_ID_TBL(J)
AND L_ACRN_TBL(J) <> 'N/A';
UPDATE PO_DISTRIBUTIONS_ALL
SET ACRN = L_ACRN_TBL(J)
WHERE PO_DISTRIBUTION_ID = L_DIST_ID_TBL(J)
AND L_ACRN_TBL(J) <> 'N/A';
SELECT INDEX_NUM2, NUM1 INTO L_DRAFT_ID, L_HEADER_ID
FROM PO_SESSION_GT
WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID' AND ROWNUM < 2;
SELECT DISTINCT PO_LINE_ID
BULK COLLECT INTO L_LINE_ID_TBL
FROM PO_DISTRIBUTIONS_GT;
SELECT INDEX_NUM1
BULK COLLECT INTO L_LINE_LOC_ID_TBL
FROM PO_SESSION_GT
WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID';
SELECT PO_DISTRIBUTION_ID
BULK COLLECT INTO L_DIST_ID_TBL
FROM PO_DISTRIBUTIONS_GT;
SELECT DISTINCT 'PACKAGE INSTALLED'
INTO L_JL_INSTALLED
FROM USER_OBJECTS
WHERE OBJECT_NAME = 'JG_GLOBE_UTIL_PKG'
AND OBJECT_TYPE = 'PACKAGE BODY';
DEBUG(D_MODULE, D_POSITION, 'INSERTING INTO PO_DISTRIBUTIONS_DRAFT_ALL');
INSERT INTO PO_DISTRIBUTIONS_DRAFT_ALL --
(PO_DISTRIBUTION_ID,
DRAFT_ID,
--WHO COLUMNS
---------------------------------------------------------------
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
---------------------------------------------------------------
PO_HEADER_ID,
PO_LINE_ID,
LINE_LOCATION_ID,
PO_RELEASE_ID,
REQ_DISTRIBUTION_ID,
SET_OF_BOOKS_ID,
CODE_COMBINATION_ID,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_PERSON_ID,
QUANTITY_ORDERED,
QUANTITY_DELIVERED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
AMOUNT_ORDERED,
AMOUNT_DELIVERED,
AMOUNT_CANCELLED,
AMOUNT_BILLED,
RATE_DATE,
RATE,
ACCRUED_FLAG,
ENCUMBERED_FLAG,
GL_ENCUMBERED_DATE,
GL_ENCUMBERED_PERIOD_NAME,
DISTRIBUTION_NUM,
DESTINATION_TYPE_CODE,
DESTINATION_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,
BUDGET_ACCOUNT_ID,
ACCRUAL_ACCOUNT_ID,
VARIANCE_ACCOUNT_ID,
--< SHARED PROC FPJ START >
DEST_CHARGE_ACCOUNT_ID,
DEST_VARIANCE_ACCOUNT_ID,
--< SHARED PROC FPJ END >
WIP_ENTITY_ID,
WIP_LINE_ID,
WIP_REPETITIVE_SCHEDULE_ID,
WIP_OPERATION_SEQ_NUM,
WIP_RESOURCE_SEQ_NUM,
BOM_RESOURCE_ID,
PREVENT_ENCUMBRANCE_FLAG,
PROJECT_ID,
TASK_ID,
END_ITEM_UNIT_NUMBER,
EXPENDITURE_TYPE,
PROJECT_ACCOUNTING_CONTEXT,
DESTINATION_CONTEXT,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ITEM_DATE,
ACCRUE_ON_RECEIPT_FLAG,
KANBAN_CARD_ID,
TAX_RECOVERY_OVERRIDE_FLAG,
RECOVERY_RATE,
AWARD_ID,
OKE_CONTRACT_LINE_ID,
OKE_CONTRACT_DELIVERABLE_ID,
ORG_ID,
DISTRIBUTION_TYPE,
TAX_ATTRIBUTE_UPDATE_CODE,
--PARTIAL FUNDING ATTRIBUTES
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED ,
CHANGE_IN_FUNDED_VALUE,
GROUP_LINE_ID,
CLM_MISC_LOA,
CLM_DEFENCE_FUNDING,
CLM_FMS_CASE_NUMBER,
CLM_AGENCY_ACCT_IDENTIFIER,
CHANGE_STATUS,
QUANTITY_FINANCED,
AMOUNT_FINANCED,
QUANTITY_RECOUPED,
AMOUNT_RECOUPED,
RETAINAGE_WITHHELD_AMOUNT,
RETAINAGE_RELEASED_AMOUNT,
PAR_DRAFT_ID, -- FOR LINKING PAR
PAR_DISTRIBUTION_ID -- FOR LINKING PAR DISTRIBUTION
)
SELECT PO_DISTRIBUTION_ID,
DRAFT_ID,
--WHO COLUMNS
---------------------------------------------------------------
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
---------------------------------------------------------------
PO_HEADER_ID,
PO_LINE_ID,
LINE_LOCATION_ID,
PO_RELEASE_ID,
REQ_DISTRIBUTION_ID,
SET_OF_BOOKS_ID,
CODE_COMBINATION_ID,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_PERSON_ID,
QUANTITY_ORDERED,
QUANTITY_DELIVERED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
AMOUNT_ORDERED,
AMOUNT_DELIVERED,
AMOUNT_CANCELLED,
AMOUNT_BILLED,
RATE_DATE,
RATE,
ACCRUED_FLAG,
ENCUMBERED_FLAG,
GL_ENCUMBERED_DATE,
GL_ENCUMBERED_PERIOD_NAME,
DISTRIBUTION_NUM,
DESTINATION_TYPE_CODE,
DESTINATION_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,
BUDGET_ACCOUNT_ID,
ACCRUAL_ACCOUNT_ID,
VARIANCE_ACCOUNT_ID,
DEST_CHARGE_ACCOUNT_ID,
DEST_VARIANCE_ACCOUNT_ID,
WIP_ENTITY_ID,
WIP_LINE_ID,
WIP_REPETITIVE_SCHEDULE_ID,
WIP_OPERATION_SEQ_NUM,
WIP_RESOURCE_SEQ_NUM,
BOM_RESOURCE_ID,
PREVENT_ENCUMBRANCE_FLAG,
PROJECT_ID,
TASK_ID,
END_ITEM_UNIT_NUMBER,
EXPENDITURE_TYPE,
PROJECT_ACCOUNTING_CONTEXT,
DESTINATION_CONTEXT,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ITEM_DATE,
ACCRUE_ON_RECEIPT_FLAG,
KANBAN_CARD_ID,
TAX_RECOVERY_OVERRIDE_FLAG, --
RECOVERY_RATE,
AWARD_ID,
OKE_CONTRACT_LINE_ID,
OKE_CONTRACT_DELIVERABLE_ID,
ORG_ID,
DISTRIBUTION_TYPE,
'CREATE',
--PARTIAL FUNDING ATTRIBUTES
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED ,
FUNDED_VALUE,
GROUP_LINE_ID,
CLM_MISC_LOA,
CLM_DEFENCE_FUNDING,
CLM_FMS_CASE_NUMBER,
CLM_AGENCY_ACCT_IDENTIFIER,
'NEW',
0, --QUANTITY_FINANCED
0, --AMOUNT_FINANCED
0, --QUANTITY_RECOUPED
0, --QUANTITY_RECOUPED
0, --RETAINAGE_WITHHELD_AMOUNT
0, --RETAINAGE_RELEASED_AMOUNT
PAR_DRAFT_ID, -- FOR LINKING PAR
PAR_DISTRIBUTION_ID -- FOR LINKING PAR DISTRIBUTION
FROM PO_DISTRIBUTIONS_GT;
DEBUG(D_MODULE, D_POSITION, 'CALLING UPDATE_AWARD_DISTRIBUTIONS');
PO_AUTO_DIST_PROCESS_PVT.UPDATE_AWARD_DISTRIBUTIONS
(
P_INTF_HEADER_ID => L_HEADER_ID,
P_INTF_LINE_ID => L_LINE_ID_TBL(I),
P_TABLE_TYPE => 'ALL',
P_PO_LINE_ID => NULL
);
DEBUG(D_MODULE, D_POSITION, 'INSERTING INTO PO_DISTRIBUTIONS_ALL');
INSERT INTO PO_DISTRIBUTIONS_ALL (
PO_DISTRIBUTION_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
PO_HEADER_ID ,
PO_LINE_ID ,
LINE_LOCATION_ID ,
SET_OF_BOOKS_ID ,
CODE_COMBINATION_ID ,
QUANTITY_ORDERED ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
PO_RELEASE_ID ,
QUANTITY_DELIVERED ,
QUANTITY_BILLED ,
QUANTITY_CANCELLED ,
REQ_HEADER_REFERENCE_NUM ,
REQ_LINE_REFERENCE_NUM ,
REQ_DISTRIBUTION_ID ,
DELIVER_TO_LOCATION_ID ,
DELIVER_TO_PERSON_ID ,
RATE_DATE ,
RATE ,
AMOUNT_BILLED ,
ACCRUED_FLAG ,
ENCUMBERED_FLAG ,
ENCUMBERED_AMOUNT ,
UNENCUMBERED_QUANTITY ,
UNENCUMBERED_AMOUNT ,
FAILED_FUNDS_LOOKUP_CODE ,
GL_ENCUMBERED_DATE ,
GL_ENCUMBERED_PERIOD_NAME ,
GL_CANCELLED_DATE ,
DESTINATION_TYPE_CODE ,
DESTINATION_ORGANIZATION_ID ,
DESTINATION_SUBINVENTORY ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
WIP_ENTITY_ID ,
WIP_OPERATION_SEQ_NUM ,
WIP_RESOURCE_SEQ_NUM ,
WIP_REPETITIVE_SCHEDULE_ID ,
WIP_LINE_ID ,
BOM_RESOURCE_ID ,
BUDGET_ACCOUNT_ID ,
ACCRUAL_ACCOUNT_ID ,
VARIANCE_ACCOUNT_ID ,
PREVENT_ENCUMBRANCE_FLAG ,
GOVERNMENT_CONTEXT ,
DESTINATION_CONTEXT ,
DISTRIBUTION_NUM ,
SOURCE_DISTRIBUTION_ID ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
PROJECT_ACCOUNTING_CONTEXT ,
EXPENDITURE_ORGANIZATION_ID ,
GL_CLOSED_DATE ,
ACCRUE_ON_RECEIPT_FLAG ,
EXPENDITURE_ITEM_DATE ,
ORG_ID ,
KANBAN_CARD_ID ,
AWARD_ID ,
MRC_RATE_DATE ,
MRC_RATE ,
MRC_ENCUMBERED_AMOUNT ,
MRC_UNENCUMBERED_AMOUNT ,
END_ITEM_UNIT_NUMBER ,
TAX_RECOVERY_OVERRIDE_FLAG ,
RECOVERABLE_TAX ,
NONRECOVERABLE_TAX ,
RECOVERY_RATE ,
OKE_CONTRACT_LINE_ID ,
OKE_CONTRACT_DELIVERABLE_ID ,
AMOUNT_ORDERED ,
AMOUNT_DELIVERED ,
AMOUNT_CANCELLED ,
DISTRIBUTION_TYPE ,
AMOUNT_TO_ENCUMBER ,
INVOICE_ADJUSTMENT_FLAG ,
DEST_CHARGE_ACCOUNT_ID ,
DEST_VARIANCE_ACCOUNT_ID ,
TAX_ATTRIBUTE_UPDATE_CODE ,
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED ,
CHANGE_IN_FUNDED_VALUE,
GROUP_LINE_ID ,
CLM_MISC_LOA,
CLM_DEFENCE_FUNDING,
CLM_FMS_CASE_NUMBER,
CLM_AGENCY_ACCT_IDENTIFIER,
QUANTITY_FINANCED,
AMOUNT_FINANCED,
QUANTITY_RECOUPED,
AMOUNT_RECOUPED,
RETAINAGE_WITHHELD_AMOUNT,
RETAINAGE_RELEASED_AMOUNT
)
SELECT
PO_DISTRIBUTION_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
PO_HEADER_ID ,
PO_LINE_ID ,
LINE_LOCATION_ID ,
SET_OF_BOOKS_ID ,
CODE_COMBINATION_ID ,
QUANTITY_ORDERED ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
PO_RELEASE_ID ,
QUANTITY_DELIVERED ,
QUANTITY_BILLED ,
QUANTITY_CANCELLED ,
REQ_HEADER_REFERENCE_NUM ,
REQ_LINE_REFERENCE_NUM ,
REQ_DISTRIBUTION_ID ,
DELIVER_TO_LOCATION_ID ,
DELIVER_TO_PERSON_ID ,
RATE_DATE ,
RATE ,
AMOUNT_BILLED ,
ACCRUED_FLAG ,
ENCUMBERED_FLAG ,
ENCUMBERED_AMOUNT ,
UNENCUMBERED_QUANTITY ,
UNENCUMBERED_AMOUNT ,
FAILED_FUNDS_LOOKUP_CODE ,
GL_ENCUMBERED_DATE ,
GL_ENCUMBERED_PERIOD_NAME ,
GL_CANCELLED_DATE ,
DESTINATION_TYPE_CODE ,
DESTINATION_ORGANIZATION_ID ,
DESTINATION_SUBINVENTORY ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
WIP_ENTITY_ID ,
WIP_OPERATION_SEQ_NUM ,
WIP_RESOURCE_SEQ_NUM ,
WIP_REPETITIVE_SCHEDULE_ID ,
WIP_LINE_ID ,
BOM_RESOURCE_ID ,
BUDGET_ACCOUNT_ID ,
ACCRUAL_ACCOUNT_ID ,
VARIANCE_ACCOUNT_ID ,
PREVENT_ENCUMBRANCE_FLAG ,
GOVERNMENT_CONTEXT ,
DESTINATION_CONTEXT ,
DISTRIBUTION_NUM ,
SOURCE_DISTRIBUTION_ID ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
PROJECT_ACCOUNTING_CONTEXT ,
EXPENDITURE_ORGANIZATION_ID ,
GL_CLOSED_DATE ,
ACCRUE_ON_RECEIPT_FLAG ,
EXPENDITURE_ITEM_DATE ,
ORG_ID ,
KANBAN_CARD_ID ,
AWARD_ID ,
MRC_RATE_DATE ,
MRC_RATE ,
MRC_ENCUMBERED_AMOUNT ,
MRC_UNENCUMBERED_AMOUNT ,
END_ITEM_UNIT_NUMBER ,
TAX_RECOVERY_OVERRIDE_FLAG ,
RECOVERABLE_TAX ,
NONRECOVERABLE_TAX ,
RECOVERY_RATE ,
OKE_CONTRACT_LINE_ID ,
OKE_CONTRACT_DELIVERABLE_ID ,
AMOUNT_ORDERED ,
AMOUNT_DELIVERED ,
AMOUNT_CANCELLED ,
DISTRIBUTION_TYPE ,
AMOUNT_TO_ENCUMBER ,
NULL, --INVOICE_ADJUSTMENT_FLAG ,
DEST_CHARGE_ACCOUNT_ID ,
DEST_VARIANCE_ACCOUNT_ID ,
'CREATE' ,
PARTIAL_FUNDED_FLAG,
FUNDED_VALUE,
QUANTITY_FUNDED,
AMOUNT_FUNDED ,
FUNDED_VALUE,
GROUP_LINE_ID ,
CLM_MISC_LOA,
CLM_DEFENCE_FUNDING,
CLM_FMS_CASE_NUMBER,
CLM_AGENCY_ACCT_IDENTIFIER,
0, --QUANTITY_FINANCED
0, --AMOUNT_FINANCED
0, --QUANTITY_RECOUPED
0, --QUANTITY_RECOUPED
0, --RETAINAGE_WITHHELD_AMOUNT
0 --RETAINAGE_RELEASED_AMOUNT
FROM PO_DISTRIBUTIONS_GT;
DEBUG(D_MODULE, D_POSITION, 'CALLING UPDATE_AWARD_DISTRIBUTIONS');
PO_INTERFACE_S.UPDATE_AWARD_DISTRIBUTIONS
(
P_TABLE_TYPE => 'ALL',
P_PO_LINE_ID => L_LINE_ID_TBL(I)
);
SELECT DISTINCT ORG_ID
INTO L_ORG_ID FROM PO_DISTRIBUTIONS_GT
where rownum <2;
--looping thru the line_id array and updating the reqs_in_pool_flag for the entire structure(including slins and options) of the selected line
FOR i in 1..p_requisition_line_id.COUNT LOOP
UPDATE po_requisition_lines_all
SET reqs_in_pool_flag = null,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE ((requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i) )
OR (group_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i))
OR (requisition_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i)))
OR (group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i)))
OR (clm_base_line_num = p_requisition_line_id(i))
OR (clm_base_line_num IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i)))
OR (group_line_id IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_requisition_line_id(i)))
OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_requisition_line_id(i)))
OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num = p_requisition_line_id(i)))
OR (clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i))))
OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i)) )))
OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
WHERE requisition_line_id = p_requisition_line_id(i) AND requisition_header_id = p_requisition_header_id(i)) )))
);
DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' ReqLineId: '|| p_requisition_line_id(i) || ' Updated no. of rows: '|| sql%rowcount);
delete_flag_tbl PO_TBL_VARCHAR1;
SELECT index_num2
INTO l_draft_id
FROM po_session_gt
WHERE index_char1 = 'LINKREQDIST_LINELOCID'
AND ROWNUM < 2;
SELECT PLL.line_location_id,
l_draft_id,
'N'
BULK COLLECT
INTO po_line_loc_id_tbl,
draft_id_tbl,
delete_flag_tbl
FROM po_line_locations_all PLL
WHERE EXISTS(
SELECT 'Shipment exists in txn'
FROM po_distributions_gt GT
WHERE PLL.line_location_id = GT.line_location_id)
AND NOT EXISTS(
SELECT 'Shipment exists in draft'
FROM po_line_locations_draft_all PLLD
WHERE PLLD.line_location_id = PLL.line_location_id
AND PLLD.draft_id = l_draft_id);
p_delete_flag_tbl => delete_flag_tbl,
x_record_already_exist_tbl => x_record_already_exist_tbl);
UPDATE po_line_locations_draft_all PLLD
SET PLLD.change_status = 'NOCHANGE'
WHERE PLLD.line_location_id = po_line_loc_id_tbl(i)
AND PLLD.draft_id = l_draft_id;
SELECT PL.po_line_id,
l_draft_id,
'N'
BULK COLLECT
INTO po_line_id_tbl,
draft_id_tbl,
delete_flag_tbl
FROM po_lines_all PL
WHERE EXISTS(
SELECT 'Line exists in txn'
FROM po_distributions_gt GT
WHERE PL.po_line_id = GT.po_line_id)
AND NOT EXISTS(
SELECT 'Line exists in draft'
FROM po_lines_draft_all PLD
WHERE PLD.po_line_id = PL.po_line_id
AND PLD.draft_id = l_draft_id);
p_delete_flag_tbl => delete_flag_tbl,
x_record_already_exist_tbl => x_record_already_exist_tbl);
UPDATE po_lines_draft_all PLD
SET PLD.change_status = 'NOCHANGE'
WHERE PLD.po_line_id = po_line_id_tbl(i)
AND PLD.draft_id = l_draft_id;
FOR SELECTED SCHEDULES, REQUISITION AND NEW DISTRIBUTIONS FROM
PO_SESSION_GT AND PO_DISTRIBUTIONS_GT');
SELECT DISTINCT reqLine.REQUISITION_HEADER_ID, reqLine.REQUISITION_LINE_ID
BULK COLLECT INTO L_REQ_HEADER_ID_TBL, L_REQ_LINE_ID_TBL
FROM PO_DISTRIBUTIONS_GT GT, po_req_distributions_all reqDist, po_requisition_lines_all reqLine
WHERE GT.REQ_DISTRIBUTION_ID = reqDist.DISTRIBUTION_ID AND
reqDist.requisition_line_id = reqLine.requisition_line_id;
DEBUG(D_MODULE, D_POSITION, 'Update the requisition line to set the req pool flag to null');
DEBUG(D_MODULE, D_POSITION, 'Update the requisition line to line id, line location id, draft if and linked po cound');
SELECT Nvl(reqDist.info_line_id,REQUISITION_LINE_ID) REQ_ID
FROM PO_DISTRIBUTIONS_GT GT, po_req_distributions_all reqDist
WHERE GT.REQ_DISTRIBUTION_ID = reqDist.DISTRIBUTION_ID
) LOOP
UPDATE po_requisition_lines_all
SET PO_LINE_ID = -1,
LINE_LOCATION_ID = -1,
PO_DRAFT_ID = NULL,
LINKED_PO_COUNT = Nvl(LINKED_PO_COUNT,0) + 1
WHERE REQUISITION_LINE_ID = req_cur.REQ_ID;
DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' Updated no. of rows: '|| sql%rowcount);
DELETE PO_DISTRIBUTIONS_GT;
DELETE PO_SESSION_GT WHERE INDEX_CHAR1 = 'LINKREQDIST_REQDISTID';
DELETE PO_SESSION_GT WHERE INDEX_CHAR1 = 'LINKREQDIST_LINELOCID';
DEBUG(D_MODULE, D_POSITION, 'progress:='||D_POSITION||' Session data deleted');