[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_TABLE IS
BEGIN
/*SRW.MESSAGE(101
,'delete_table() << ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
DELETE FROM CST_PAC_ACCRUAL_RECONCILE_TEMP
WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND COST_TYPE_ID = P_COST_TYPE
AND COST_GROUP_ID = P_COST_GROUP;
DELETE FROM CST_PAC_ACCRUAL_ACCOUNTS_TEMP
WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY;
,'delete_table() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
,'delete_table() >X ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
END DELETE_TABLE;
PROCEDURE INSERT_AP_DATA IS
l_disp_field VARCHAR2(80);
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT /*+ LEADING(xah) */ -- rgangara for perf bug 7563374
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
sysdate,
NULL,
P_ORG_ID,
P_MASTER_ORG_ID,
XAL.CODE_COMBINATION_ID,
'AP: not yet processed',
NULL,
XAH.ACCOUNTING_DATE,
API.INVOICE_NUM,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
POL.ITEM_ID,
POL.UNIT_MEAS_LOOKUP_CODE,
POL.UNIT_MEAS_LOOKUP_CODE,
NVL(AID.QUANTITY_INVOICED
,0),
NULL,
POH.PO_HEADER_ID,
POH.SEGMENT1,
POL.LINE_NUM,
POL.PO_LINE_ID,
POD.PO_DISTRIBUTION_ID,
API.VENDOR_ID,
POV.VENDOR_NAME,
POV.VENDOR_NAME_ALT,
ROUND(DECODE(API.EXCHANGE_RATE
,NULL
,NVL(((NVL(XAL.ACCOUNTED_DR
,XAL.ACCOUNTED_CR)) / NVL(AID.QUANTITY_INVOICED
,1))
,0)
,NVL(((NVL(XAL.ACCOUNTED_DR
,XAL.ACCOUNTED_CR)) / NVL(AID.QUANTITY_INVOICED
,1))
,0) * API.EXCHANGE_RATE)
,P_EXT_PREC),
AID.INVOICE_ID,
AID.DISTRIBUTION_LINE_NUMBER,
NULL,
ROUND(DECODE(API.INVOICE_CURRENCY_CODE
,P_CURRENCY_CODE
,(NVL(XAL.ENTERED_DR
,(-1) * XAL.ENTERED_CR))
,(NVL(XAL.ACCOUNTED_DR
,(-1) * XAL.ACCOUNTED_CR))) / ROUND_UNIT) * ROUND_UNIT,
NULL,
'AP',
'N',
NULL,
POD.DESTINATION_TYPE_CODE,
NULL,
NULL,
POD.LINE_LOCATION_ID,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
AP_INVOICES_ALL API,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
PO_VENDORS POV,
PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL POLL,
PO_LINES_ALL POL,
PO_HEADERS_ALL POH,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
WHERE CAA.ACCRUAL_ACCOUNT_ID = XAL.CODE_COMBINATION_ID
AND XAH.APPLICATION_ID = 200
AND XAL.APPLICATION_ID = 200
AND XDL.APPLICATION_ID = 200
AND XAL.ACCOUNTING_CLASS_CODE = 'ACCRUAL'
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO -- Bug 7563374 removed NVL as dates cannot be NULL
AND XAH.GL_TRANSFER_STATUS_CODE = 'Y' -- Bug 7563374 Changed the condition
AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
AND AID.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
AND AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POLL.SHIP_TO_ORGANIZATION_ID = P_ORG_ID
AND POL.PO_LINE_ID = POD.PO_LINE_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND API.INVOICE_ID = AID.INVOICE_ID
AND AID.PO_DISTRIBUTION_ID IS NOT NULL
AND AID.RCV_TRANSACTION_ID IS NULL
AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM ,POV.VENDOR_NAME) AND NVL(P_VENDOR_TO ,POV.VENDOR_NAME)
AND XAH.LEDGER_ID = P_SET_OF_BOOKS_ID;
SELECT displayed_field
INTO l_disp_field
FROM PO_LOOKUP_CODES PLU
WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
AND LOOKUP_CODE = 'AP INVOICE PRICE VAR';
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,INVOICE_PRICE_VARIANCE
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT /*+ LEADING(xah) */
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
sysdate,
NULL,
P_ORG_ID,
P_MASTER_ORG_ID,
XAL.CODE_COMBINATION_ID,
l_disp_field, --PLU.DISPLAYED_FIELD -- changed for perf Bug 7563374 rgangara
NULL,
XAH.ACCOUNTING_DATE,
API.INVOICE_NUM,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
POL.ITEM_ID,
POL.UNIT_MEAS_LOOKUP_CODE,
POL.UNIT_MEAS_LOOKUP_CODE,
0,
NULL,
POH.PO_HEADER_ID,
POH.SEGMENT1,
POL.LINE_NUM,
POL.PO_LINE_ID,
POD.PO_DISTRIBUTION_ID,
API.VENDOR_ID,
POV.VENDOR_NAME,
POV.VENDOR_NAME_ALT,
0,
AID.INVOICE_ID,
AID.DISTRIBUTION_LINE_NUMBER,
NULL,
ROUND(DECODE(API.INVOICE_CURRENCY_CODE
,P_CURRENCY_CODE
,(NVL(XAL.ENTERED_DR
,(-1) * XAL.ENTERED_CR))
,(NVL(XAL.ACCOUNTED_DR
,(-1) * XAL.ACCOUNTED_DR))) / ROUND_UNIT) * ROUND_UNIT,
ROUND(DECODE(API.INVOICE_CURRENCY_CODE
,P_CURRENCY_CODE
,(NVL(XAL.ENTERED_DR
,(-1) * XAL.ENTERED_CR))
,(NVL(XAL.ACCOUNTED_DR
,(-1) * XAL.ACCOUNTED_CR))) / ROUND_UNIT) * ROUND_UNIT * ( - 1 ),
NULL,
'AP',
'N',
NULL,
POD.DESTINATION_TYPE_CODE,
NULL,
NULL,
POD.LINE_LOCATION_ID,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
AP_INVOICES_ALL API,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
PO_VENDORS POV,
PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL POLL,
PO_LINES_ALL POL,
PO_HEADERS_ALL POH,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
WHERE CAA.ACCRUAL_ACCOUNT_ID = XAL.CODE_COMBINATION_ID
AND XAH.APPLICATION_ID = 200
AND XAL.APPLICATION_ID = 200
AND XDL.APPLICATION_ID = 200
AND XAL.ACCOUNTING_CLASS_CODE = 'IPV'
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
AND XAH.EVENT_ID = AID.ACCOUNTING_EVENT_ID
AND AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POLL.SHIP_TO_ORGANIZATION_ID = P_ORG_ID
AND POL.PO_LINE_ID = POD.PO_LINE_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND API.INVOICE_ID = AID.INVOICE_ID
AND AID.PO_DISTRIBUTION_ID IS NOT NULL
AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM ,POV.VENDOR_NAME) AND NVL(P_VENDOR_TO ,POV.VENDOR_NAME)
AND EXISTS (
SELECT
'X'
FROM
AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE AIDA.RELATED_ID = AID.INVOICE_DISTRIBUTION_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE = 'IPV'
AND ( ( AIDA.BASE_AMOUNT IS NOT NULL
OR AIDA.BASE_AMOUNT <> 0 )
OR ( AIDA.AMOUNT IS NOT NULL
OR AIDA.AMOUNT <> 0 ) ) )
AND XAH.LEDGER_ID = P_SET_OF_BOOKS_ID
/*Support for LCM*/
AND NVL(POLL.LCM_FLAG,'N') = 'N';
SELECT displayed_field
INTO l_disp_field
FROM PO_LOOKUP_CODES PLU
WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
AND PLU.LOOKUP_CODE = 'AP EXCHANGE RATE VAR';
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,INVOICE_PRICE_VARIANCE
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT /*+ LEADING(xah) */
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
sysdate,
NULL,
P_ORG_ID,
P_MASTER_ORG_ID,
XAL.CODE_COMBINATION_ID,
l_disp_field, --PLU.DISPLAYED_FIELD,
NULL,
XAH.ACCOUNTING_DATE,
API.INVOICE_NUM,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
POL.ITEM_ID,
POL.UNIT_MEAS_LOOKUP_CODE,
POL.UNIT_MEAS_LOOKUP_CODE,
0,
NULL,
POH.PO_HEADER_ID,
POH.SEGMENT1,
POL.LINE_NUM,
POL.PO_LINE_ID,
POD.PO_DISTRIBUTION_ID,
API.VENDOR_ID,
POV.VENDOR_NAME,
POV.VENDOR_NAME_ALT,
0,
AID.INVOICE_ID,
AID.DISTRIBUTION_LINE_NUMBER,
NULL,
ROUND(NVL(XAL.ENTERED_DR
,(-1) * XAL.ENTERED_CR) / ROUND_UNIT) * ROUND_UNIT,
ROUND(NVL(XAL.ENTERED_DR
,(-1) * XAL.ENTERED_DR) / ROUND_UNIT) * ROUND_UNIT * ( - 1 ),
NULL,
'AP',
'N',
NULL,
POD.DESTINATION_TYPE_CODE,
NULL,
NULL,
POD.LINE_LOCATION_ID,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
AP_INVOICES_ALL API,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
PO_VENDORS POV,
PO_DISTRIBUTIONS_ALL POD,
PO_LINE_LOCATIONS_ALL POLL,
PO_LINES_ALL POL,
PO_HEADERS_ALL POH,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
WHERE CAA.ACCRUAL_ACCOUNT_ID = XAL.CODE_COMBINATION_ID
AND XAH.APPLICATION_ID = 200
AND XAL.APPLICATION_ID = 200
AND XDL.APPLICATION_ID = 200
AND XAL.ACCOUNTING_CLASS_CODE = 'ERV'
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
AND AID.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
AND AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POLL.SHIP_TO_ORGANIZATION_ID = P_ORG_ID
AND POL.PO_LINE_ID = POD.PO_LINE_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.ORG_ID = P_OPERATING_UNIT
AND POV.VENDOR_ID = POH.VENDOR_ID
AND API.INVOICE_ID = AID.INVOICE_ID
AND AID.PO_DISTRIBUTION_ID IS NOT NULL
AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
,POV.VENDOR_NAME)
AND NVL(P_VENDOR_TO
,POV.VENDOR_NAME)
AND EXISTS (
SELECT
'X'
FROM
AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE AIDA.RELATED_ID = AID.INVOICE_DISTRIBUTION_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE = 'ERV'
AND ( AIDA.AMOUNT IS NOT NULL
OR AIDA.AMOUNT <> 0 ) )
AND XAH.LEDGER_ID = P_SET_OF_BOOKS_ID
/*Support for LCM*/
AND NVL(POLL.LCM_FLAG,'N') = 'N';
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT /*+ LEADING(xah) */
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
sysdate,
NULL,
P_ORG_ID,
P_MASTER_ORG_ID,
XAL.CODE_COMBINATION_ID,
'AP: not yet processed',
NULL,
XAH.ACCOUNTING_DATE,
API.INVOICE_NUM,
RSH.RECEIPT_NUM,
NULL,
NULL,
NULL,
NULL,
NULL,
POL.ITEM_ID,
POL.UNIT_MEAS_LOOKUP_CODE,
POL.UNIT_MEAS_LOOKUP_CODE,
NVL(AID.QUANTITY_INVOICED
,0),
NULL,
POH.PO_HEADER_ID,
POH.SEGMENT1,
POL.LINE_NUM,
POL.PO_LINE_ID,
RT.PO_DISTRIBUTION_ID,
API.VENDOR_ID,
POV.VENDOR_NAME,
POV.VENDOR_NAME_ALT,
ROUND(DECODE(API.EXCHANGE_RATE
,NULL
,NVL(((NVL(XAL.ACCOUNTED_DR
,XAL.ACCOUNTED_CR)) / NVL(AID.QUANTITY_INVOICED
,1))
,0)
,NVL(((NVL(XAL.ACCOUNTED_DR
,XAL.ACCOUNTED_CR)) / NVL(AID.QUANTITY_INVOICED
,1))
,0) * API.EXCHANGE_RATE)
,P_EXT_PREC),
AID.INVOICE_ID,
AID.DISTRIBUTION_LINE_NUMBER,
NULL,
ROUND(DECODE(API.INVOICE_CURRENCY_CODE
,P_CURRENCY_CODE
,(NVL(XAL.ENTERED_DR
,(-1) * XAL.ENTERED_CR))
,(NVL(XAL.ACCOUNTED_DR
,(-1) * XAL.ACCOUNTED_CR))) / ROUND_UNIT) * ROUND_UNIT,
NULL,
'AP',
'N',
NULL,
RT.DESTINATION_TYPE_CODE,
NULL,
NULL,
POLL.LINE_LOCATION_ID,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
AP_INVOICES_ALL API,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
PO_VENDORS POV,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL POLL,
PO_HEADERS_ALL POH,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
WHERE CAA.ACCRUAL_ACCOUNT_ID = XAL.CODE_COMBINATION_ID
AND XAH.APPLICATION_ID = 200
AND XAL.APPLICATION_ID = 200
AND XDL.APPLICATION_ID = 200
AND XAL.ACCOUNTING_CLASS_CODE = 'ACCRUAL'
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
AND AID.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
AND AID.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND NVL(RT.ORGANIZATION_ID
,P_ORG_ID) = P_ORG_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND API.INVOICE_ID = AID.INVOICE_ID
AND AID.RCV_TRANSACTION_ID IS NOT NULL
AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
,POV.VENDOR_NAME)
AND NVL(P_VENDOR_TO
,POV.VENDOR_NAME)
AND XAH.LEDGER_ID = P_SET_OF_BOOKS_ID;
,'insert_ap_data() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
,'insert_ap_data() >X ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
END INSERT_AP_DATA;
PROCEDURE INSERT_AP_MISC IS
BEGIN
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT /*+ LEADING(xah) */
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
sysdate,
NULL,
P_MASTER_ORG_ID,
P_MASTER_ORG_ID,
XAL.CODE_COMBINATION_ID,
'AP: not yet processed',
NULL,
XAH.ACCOUNTING_DATE,
API.INVOICE_NUM,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NVL(AID.QUANTITY_INVOICED
,0),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
API.VENDOR_ID,
POV.VENDOR_NAME,
POV.VENDOR_NAME_ALT,
ROUND(DECODE(API.EXCHANGE_RATE
,NULL
,NVL(AID.UNIT_PRICE
,0)
,NVL(AID.UNIT_PRICE
,0) * API.EXCHANGE_RATE)
,P_EXT_PREC),
AID.INVOICE_ID,
AID.DISTRIBUTION_LINE_NUMBER,
NULL,
ROUND(NVL((NVL(XAL.ACCOUNTED_DR
,(-1) * XAL.ACCOUNTED_CR))
,(NVL(XAL.ENTERED_DR
,(-1) * XAL.ENTERED_CR))) / ROUND_UNIT) * ROUND_UNIT,
NULL,
'AP',
'N',
NULL,
NULL,
NULL,
NULL,
NULL,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
AP_INVOICES_ALL API,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
PO_VENDORS POV,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
WHERE CAA.ACCRUAL_ACCOUNT_ID = XAL.CODE_COMBINATION_ID
AND XAH.APPLICATION_ID = 200
AND XAL.APPLICATION_ID = 200
AND XDL.APPLICATION_ID = 200
AND XAL.ACCOUNTING_CLASS_CODE = 'ACCRUAL'
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
AND AID.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
AND AID.PO_DISTRIBUTION_ID is NULL
AND AID.RCV_TRANSACTION_ID is NULL
AND API.INVOICE_ID = AID.INVOICE_ID
AND POV.VENDOR_ID = API.VENDOR_ID
AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
,POV.VENDOR_NAME)
AND NVL(P_VENDOR_TO
,POV.VENDOR_NAME)
AND XAH.LEDGER_ID = P_SET_OF_BOOKS_ID;
,'insert_ap_misc() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
,'insert_ap_misc() >X ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
END INSERT_AP_MISC;
PROCEDURE INSERT_PO_DATA IS
BEGIN
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
sysdate,
NULL,
P_ORG_ID,
P_MASTER_ORG_ID,
CAL.CODE_COMBINATION_ID,
PLC.DISPLAYED_FIELD,
RCT.TRANSACTION_TYPE,
CAH.ACCOUNTING_DATE,
NULL,
RSH.RECEIPT_NUM,
RCT.TRANSACTION_ID,
NULL,
NULL,
NULL,
NULL,
POL.ITEM_ID,
POL.UNIT_MEAS_LOOKUP_CODE,
RCT.PRIMARY_UNIT_OF_MEASURE,
DECODE(RCT.PO_DISTRIBUTION_ID
,NULL
,(NVL(RCT.PRIMARY_QUANTITY
,0) * (NVL(POD.QUANTITY_ORDERED
,0) / NVL(POLL.QUANTITY
,1)))
,NVL(RCT.PRIMARY_QUANTITY
,0)) * DECODE(CAL.ACCOUNTED_DR
,NULL
,-1
,1),
NULL,
RCT.PO_HEADER_ID,
POH.SEGMENT1,
POL.LINE_NUM,
POL.PO_LINE_ID,
POD.PO_DISTRIBUTION_ID,
POH.VENDOR_ID,
POV.VENDOR_NAME,
POV.VENDOR_NAME_ALT,
ROUND(NVL(CAL.RATE_OR_AMOUNT
,1)
,P_EXT_PREC),
NULL,
NULL,
NULL,
ROUND((NVL(CAL.ACCOUNTED_DR
,0) - NVL(CAL.ACCOUNTED_CR
,0)) / ROUND_UNIT) * ROUND_UNIT,
NULL,
'PO',
'N',
NULL,
POD.DESTINATION_TYPE_CODE,
NULL,
NULL,
POD.LINE_LOCATION_ID,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
PO_LOOKUP_CODES PLC,
RCV_SHIPMENT_HEADERS RSH,
PO_VENDORS POV,
PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL POLL,
PO_DISTRIBUTIONS_ALL POD,
RCV_TRANSACTIONS RCT,
CST_AE_HEADERS CAH,
CST_AE_LINES CAL,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
WHERE CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
AND CAH.GL_TRANSFER_FLAG = 'Y'
AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND CAH.COST_TYPE_ID = P_COST_TYPE
AND CAH.COST_GROUP_ID = P_COST_GROUP
AND RCT.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
AND RCT.SOURCE_DOCUMENT_CODE <> 'REQ'
AND RCT.TRANSACTION_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
AND PLC.LOOKUP_CODE = RCT.TRANSACTION_TYPE
AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
AND POD.PO_DISTRIBUTION_ID = CAL.PO_DISTRIBUTION_ID
AND RCT.ORGANIZATION_ID = P_ORG_ID
AND POD.ORG_ID = P_OPERATING_UNIT
AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POL.PO_LINE_ID = POD.PO_LINE_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
,POV.VENDOR_NAME)
AND NVL(P_VENDOR_TO
,POV.VENDOR_NAME)
AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
/*Support for LCM*/
AND nvl(cal.ae_line_type_code,'16') <> '38';
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,ADJUSTMENT_TRANSACTION
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
sysdate,
NULL,
P_ORG_ID,
P_MASTER_ORG_ID,
CAL.CODE_COMBINATION_ID,
PLC.DISPLAYED_FIELD,
RCT.TRANSACTION_TYPE,
1,
CAH.ACCOUNTING_DATE,
NULL,
RSH.RECEIPT_NUM,
RCT.TRANSACTION_ID,
NULL,
NULL,
NULL,
NULL,
POL.ITEM_ID,
POL.UNIT_MEAS_LOOKUP_CODE,
RCT.PRIMARY_UNIT_OF_MEASURE,
NULL,
NULL,
RCT.PO_HEADER_ID,
POH.SEGMENT1,
POL.LINE_NUM,
POL.PO_LINE_ID,
POD.PO_DISTRIBUTION_ID,
POH.VENDOR_ID,
POV.VENDOR_NAME,
POV.VENDOR_NAME_ALT,
NULL,
NULL,
NULL,
NULL,
ROUND((NVL(CAL.ACCOUNTED_DR
,0) - NVL(CAL.ACCOUNTED_CR
,0)) / ROUND_UNIT) * ROUND_UNIT,
NULL,
'PO',
'N',
NULL,
POD.DESTINATION_TYPE_CODE,
NULL,
NULL,
POD.LINE_LOCATION_ID,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
PO_LOOKUP_CODES PLC,
RCV_SHIPMENT_HEADERS RSH,
PO_VENDORS POV,
PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL POLL,
PO_DISTRIBUTIONS_ALL POD,
RCV_TRANSACTIONS RCT,
CST_AE_HEADERS CAH,
CST_AE_LINES CAL,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA,
RCV_ACCOUNTING_EVENTS RAE
WHERE CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
AND CAH.GL_TRANSFER_FLAG = 'Y'
AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND CAH.COST_TYPE_ID = P_COST_TYPE
AND CAH.COST_GROUP_ID = P_COST_GROUP
AND RCT.SOURCE_DOCUMENT_CODE <> 'REQ'
AND RAE.TRANSACTION_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
AND PLC.LOOKUP_CODE = RCT.TRANSACTION_TYPE
AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
AND POD.PO_DISTRIBUTION_ID = CAL.PO_DISTRIBUTION_ID
AND RCT.ORGANIZATION_ID = P_ORG_ID
AND POD.ORG_ID = P_OPERATING_UNIT
AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POL.PO_LINE_ID = POD.PO_LINE_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
,POV.VENDOR_NAME)
AND NVL(P_VENDOR_TO
,POV.VENDOR_NAME)
AND RAE.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID
AND RAE.EVENT_TYPE_ID in ( 7 , 8 )
AND RAE.ACCOUNTING_EVENT_ID = CAH.ACCOUNTING_EVENT_ID
AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
,'insert_po_data() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
,'insert_po_data() >X ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
END INSERT_PO_DATA;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP p1
SET
ACCRUAL_CODE = (SELECT
DISPLAYED_FIELD
FROM
PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
AND LOOKUP_CODE = 'AP PO MATCH')
WHERE ACCRUAL_CODE = 'AP: not yet processed'
AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P1.COST_TYPE_ID = P_COST_TYPE
AND P1.COST_GROUP_ID = P_COST_GROUP
AND EXISTS (
SELECT
null
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP P2
WHERE P2.INVOICE_ID is null
AND ( P2.INVENTORY_ITEM_ID = P1.INVENTORY_ITEM_ID
OR ( P2.INVENTORY_ITEM_ID is NULL
AND P2.DESTINATION_TYPE_CODE = 'EXPENSE' ) )
AND P2.PO_HEADER_ID = P1.PO_HEADER_ID
AND P2.ACCRUAL_ACCOUNT_ID = P1.ACCRUAL_ACCOUNT_ID
AND P2.DESTINATION_TYPE_CODE = P1.DESTINATION_TYPE_CODE
AND P2.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P2.COST_TYPE_ID = P_COST_TYPE
AND P2.COST_GROUP_ID = P_COST_GROUP );
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP p1
SET
ACCRUAL_CODE = (SELECT
DISPLAYED_FIELD
FROM
PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
AND LOOKUP_CODE = 'AP ITEM MATCH')
WHERE ACCRUAL_CODE = 'AP: not yet processed'
AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P1.COST_TYPE_ID = P_COST_TYPE
AND P1.COST_GROUP_ID = P_COST_GROUP
AND EXISTS (
SELECT
null
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP P2
WHERE P2.INVOICE_ID is null
AND P2.INVENTORY_ITEM_ID = P1.INVENTORY_ITEM_ID
AND P2.ACCRUAL_ACCOUNT_ID = P1.ACCRUAL_ACCOUNT_ID
AND P2.DESTINATION_TYPE_CODE = P1.DESTINATION_TYPE_CODE
AND P2.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P2.COST_TYPE_ID = P_COST_TYPE
AND P2.COST_GROUP_ID = P_COST_GROUP );
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP p1
SET
ACCRUAL_CODE = (SELECT
DISPLAYED_FIELD
FROM
PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
AND LOOKUP_CODE = 'AP LINE MATCH')
WHERE ACCRUAL_CODE = 'AP: not yet processed'
AND DESTINATION_TYPE_CODE in ( 'SHOP FLOOR' , 'INVENTORY' , 'EXPENSE' )
AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P1.COST_TYPE_ID = P_COST_TYPE
AND P1.COST_GROUP_ID = P_COST_GROUP
AND EXISTS (
SELECT
NULL
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP P2
WHERE P2.INVOICE_ID is null
AND ( P2.INVENTORY_ITEM_ID = P1.INVENTORY_ITEM_ID
OR ( P1.INVENTORY_ITEM_ID IS NULL
AND P1.DESTINATION_TYPE_CODE = 'EXPENSE' ) )
AND P2.PO_HEADER_ID = P1.PO_HEADER_ID
AND P2.PO_LINE_ID = P1.PO_LINE_ID
AND P2.ACCRUAL_ACCOUNT_ID = P1.ACCRUAL_ACCOUNT_ID
AND P2.DESTINATION_TYPE_CODE = P1.DESTINATION_TYPE_CODE
AND P2.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P2.COST_TYPE_ID = P_COST_TYPE
AND P2.COST_GROUP_ID = P_COST_GROUP );
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP p1
SET
ACCRUAL_CODE = (SELECT
DISPLAYED_FIELD
FROM
PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
AND LOOKUP_CODE = 'AP NO MATCH')
WHERE ACCRUAL_CODE = 'AP: not yet processed'
AND PO_HEADER_ID IS NOT NULL
AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND COST_TYPE_ID = P_COST_TYPE
AND COST_GROUP_ID = P_COST_GROUP;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP p1
SET
ACCRUAL_CODE = (SELECT
DISPLAYED_FIELD
FROM
PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
AND LOOKUP_CODE = 'AP NO ITEM')
,INVOICE_PRICE_VARIANCE = (-1) * TRANSACTION_AMOUNT
WHERE ACCRUAL_CODE = 'AP: not yet processed'
AND P1.PO_HEADER_ID IS NOT NULL
AND P1.INVENTORY_ITEM_ID IS NOT NULL
AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND COST_TYPE_ID = P_COST_TYPE
AND COST_GROUP_ID = P_COST_GROUP
AND NOT EXISTS (
SELECT
null
FROM
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = P1.TRANSACTION_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = P1.INVENTORY_ITEM_ID );
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP
SET
ACCRUAL_CODE = (SELECT
DISPLAYED_FIELD
FROM
PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
AND LOOKUP_CODE = 'AP NO PO')
,INVOICE_PRICE_VARIANCE = (-1) * TRANSACTION_AMOUNT
WHERE ACCRUAL_CODE = 'AP: not yet processed'
AND PO_HEADER_ID IS NULL
AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND COST_TYPE_ID = P_COST_TYPE
AND COST_GROUP_ID = P_COST_GROUP;
INSERT_MFG_DATA;
INSERT_AP_DATA;
INSERT_PO_DATA;
SELECT
PO_LINE_ID,
ACCRUAL_ACCOUNT_ID,
SUM((-1) * TRANSACTION_QUANTITY),
SUM((-1) * TRANSACTION_AMOUNT)
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP P1
WHERE INVOICE_ID is null
AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P1.COST_TYPE_ID = P_COST_TYPE
AND P1.COST_GROUP_ID = P_COST_GROUP
AND TRANSACTION_DATE between P_GL_DATE_FROM
AND P_GL_DATE_TO
AND exists (
SELECT
null
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP P2
WHERE P2.PO_LINE_ID = P1.PO_LINE_ID
AND P2.LINE_LOCATION_ID = P1.LINE_LOCATION_ID
AND P2.ACCRUAL_ACCOUNT_ID = P1.ACCRUAL_ACCOUNT_ID
AND P2.INVOICE_ID is not null
AND P2.TRANSACTION_AMOUNT <> P2.AVG_RECEIPT_PRICE * P2.TRANSACTION_QUANTITY
AND P2.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P2.COST_TYPE_ID = P_COST_TYPE
AND P2.COST_GROUP_ID = P_COST_GROUP
AND TRANSACTION_DATE between P_GL_DATE_FROM
AND P_GL_DATE_TO )
GROUP BY
PO_LINE_ID,
ACCRUAL_ACCOUNT_ID;
SELECT
INVOICE_ID,
INVOICE_LINE_NUM,
TRANSACTION_QUANTITY,
TRANSACTION_AMOUNT,
TRANSACTION_AMOUNT / DECODE(TRANSACTION_QUANTITY
,0
,1
,TRANSACTION_QUANTITY)
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP
WHERE PO_LINE_ID = L_LINE_ID
AND ACCRUAL_ACCOUNT_ID = L_ACCOUNT
AND INVOICE_ID is not null
AND TRANSACTION_QUANTITY <> 0
AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND COST_TYPE_ID = P_COST_TYPE
AND COST_GROUP_ID = P_COST_GROUP
AND TRANSACTION_DATE between P_GL_DATE_FROM
AND P_GL_DATE_TO
ORDER BY
SIGN(TRANSACTION_QUANTITY),
INVOICE_NUM,
TRANSACTION_DATE;
SELECT
count(*)
INTO TXN_NUM
FROM
MTL_TRANSACTION_ACCOUNTS MTA,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP PAT
WHERE PAT.REQUEST_ID = P_CONC_REQUEST_ID
AND MTA.REFERENCE_ACCOUNT = PAT.ACCRUAL_ACCOUNT_ID
AND MTA.GL_BATCH_ID <> - 1
AND MTA.TRANSACTION_SOURCE_TYPE_ID = 1;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP p
SET
INVOICE_PRICE_VARIANCE = (-1) * TRANSACTION_AMOUNT
WHERE PO_LINE_ID is not null
AND P.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P.COST_TYPE_ID = P_COST_TYPE
AND P.COST_GROUP_ID = P_COST_GROUP
AND exists (
SELECT
NULL
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP P1
WHERE P1.PO_LINE_ID = P.PO_LINE_ID
AND P1.INVOICE_ID is null
AND P1.ACCRUAL_ACCOUNT_ID = P.ACCRUAL_ACCOUNT_ID
AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P1.COST_TYPE_ID = P_COST_TYPE
AND P1.COST_GROUP_ID = P_COST_GROUP
HAVING ( SUM(P1.TRANSACTION_QUANTITY) = 0
AND SUM(P1.TRANSACTION_AMOUNT) <> 0 ) )
AND not exists (
SELECT
NULL
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP P1
WHERE P1.ACCRUAL_ACCOUNT_ID = P.ACCRUAL_ACCOUNT_ID
AND P1.PO_LINE_ID = P.PO_LINE_ID
AND P1.INVOICE_ID is not null
AND P1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND P1.COST_TYPE_ID = P_COST_TYPE
AND P1.COST_GROUP_ID = P_COST_GROUP );
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP p
SET
INVOICE_PRICE_VARIANCE = (-1) * TRANSACTION_AMOUNT
WHERE INVOICE_ID is not null
AND NVL(TRANSACTION_QUANTITY
,0) = 0
AND TRANSACTION_AMOUNT <> 0
AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND COST_TYPE_ID = P_COST_TYPE
AND COST_GROUP_ID = P_COST_GROUP;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP
SET
INVOICE_PRICE_VARIANCE = L_IPV
WHERE INVOICE_ID = L_INV_ID
AND INVOICE_LINE_NUM = L_IVL_ID
AND TRANSACTION_QUANTITY <> 0
AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND COST_TYPE_ID = P_COST_TYPE
AND COST_GROUP_ID = P_COST_GROUP;
SELECT
count(*)
INTO L_COUNT
FROM
AP_SYSTEM_PARAMETERS_ALL
WHERE NVL(ORG_ID
,P_OPERATING_UNIT) = P_OPERATING_UNIT;
SELECT
count(*)
INTO L_COUNT
FROM
PO_SYSTEM_PARAMETERS_ALL
WHERE NVL(ORG_ID
,P_OPERATING_UNIT) = P_OPERATING_UNIT;
SELECT
EXPENSE_ACCRUAL_CODE
INTO P_ACCRUAL_CODE
FROM
PO_SYSTEM_PARAMETERS_ALL
WHERE NVL(ORG_ID
,P_OPERATING_UNIT) = P_OPERATING_UNIT;
INSERT INTO CST_PAC_ACCRUAL_ACCOUNTS_TEMP
(REQUEST_ID
,ACCRUAL_ACCOUNT_ID
,LEGAL_ENTITY_ID)
SELECT
DISTINCT
P_CONC_REQUEST_ID,
ACCRUAL_ACCOUNT_ID,
P_LEGAL_ENTITY
FROM
PO_DISTRIBUTIONS_ALL POD
WHERE POD.ACCRUAL_ACCOUNT_ID is not null
AND POD.ORG_ID = P_OPERATING_UNIT
AND NOT ( POD.DESTINATION_TYPE_CODE = 'EXPENSE'
AND P_ACCRUAL_CODE = 'PERIOD END' )
AND NOT EXISTS (
SELECT
1
FROM
CST_PAC_ACCRUAL_ACCOUNTS_TEMP A1
WHERE POD.ACCRUAL_ACCOUNT_ID = A1.ACCRUAL_ACCOUNT_ID
AND A1.REQUEST_ID = P_CONC_REQUEST_ID );
PROCEDURE UPDATE_WRITEOFF IS
BEGIN
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP part
SET
(WRITE_OFF_ID,WRITE_OFF_FLAG) = (SELECT
MAX(WRITE_OFF_ID),
DECODE(SIGN(SUM(DECODE(WRITE_OFF_CODE
,'WRITE OFF'
,1
,-1)))
,1
,'Y'
,'N')
FROM
CST_PAC_ACCRUAL_WRITE_OFFS PAWO
WHERE PAWO.INV_TRANSACTION_ID = PART.INV_TRANSACTION_ID
AND PAWO.COST_TYPE_ID = P_COST_TYPE
AND PAWO.COST_GROUP_ID = P_COST_GROUP
AND PAWO.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
GROUP BY
PAWO.INV_TRANSACTION_ID)
WHERE PART.COST_TYPE_ID = P_COST_TYPE
AND PART.COST_GROUP_ID = P_COST_GROUP
AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART.ROWID in (
SELECT
PART1.ROWID
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP PART1,
CST_PAC_ACCRUAL_WRITE_OFFS PAWO
WHERE PAWO.INV_TRANSACTION_ID = PART1.INV_TRANSACTION_ID
AND PART1.COST_TYPE_ID = P_COST_TYPE
AND PART1.COST_GROUP_ID = P_COST_GROUP
AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART1.COST_TYPE_ID = PAWO.COST_TYPE_ID
AND PART1.COST_GROUP_ID = PAWO.COST_GROUP_ID
AND PART1.LEGAL_ENTITY_ID = PAWO.LEGAL_ENTITY_ID );
,'update_inventory() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP part
SET
(WRITE_OFF_ID,WRITE_OFF_FLAG) = (SELECT
MAX(WRITE_OFF_ID),
DECODE(SIGN(SUM(DECODE(WRITE_OFF_CODE
,'WRITE OFF'
,1
,-1)))
,1
,'Y'
,'N')
FROM
CST_PAC_ACCRUAL_WRITE_OFFS PAWO
WHERE PAWO.WIP_TRANSACTION_ID = PART.WIP_TRANSACTION_ID
AND PAWO.COST_TYPE_ID = P_COST_TYPE
AND PAWO.COST_GROUP_ID = P_COST_GROUP
AND PAWO.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
GROUP BY
PAWO.WIP_TRANSACTION_ID)
WHERE PART.COST_TYPE_ID = P_COST_TYPE
AND PART.COST_GROUP_ID = P_COST_GROUP
AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART.ROWID in (
SELECT
PART1.ROWID
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP PART1,
CST_PAC_ACCRUAL_WRITE_OFFS PAWO
WHERE PAWO.WIP_TRANSACTION_ID = PART1.WIP_TRANSACTION_ID
AND PART1.COST_TYPE_ID = P_COST_TYPE
AND PART1.COST_GROUP_ID = P_COST_GROUP
AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART1.COST_TYPE_ID = PAWO.COST_TYPE_ID
AND PART1.COST_GROUP_ID = PAWO.COST_GROUP_ID
AND PART1.LEGAL_ENTITY_ID = PAWO.LEGAL_ENTITY_ID );
,'update_wip() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP part
SET
(WRITE_OFF_ID,WRITE_OFF_FLAG) = (SELECT
MAX(WRITE_OFF_ID),
DECODE(SIGN(SUM(DECODE(WRITE_OFF_CODE
,'WRITE OFF'
,1
,-1)))
,1
,'Y'
,'N')
FROM
CST_PAC_ACCRUAL_WRITE_OFFS PAWO
WHERE PAWO.PO_TRANSACTION_ID = PART.PO_TRANSACTION_ID
AND PAWO.COST_TYPE_ID = P_COST_TYPE
AND PAWO.COST_GROUP_ID = P_COST_GROUP
AND PAWO.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
GROUP BY
PAWO.PO_TRANSACTION_ID)
WHERE PART.COST_TYPE_ID = P_COST_TYPE
AND PART.COST_GROUP_ID = P_COST_GROUP
AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART.ROWID in (
SELECT
PART1.ROWID
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP PART1,
CST_PAC_ACCRUAL_WRITE_OFFS PAWO
WHERE PAWO.PO_TRANSACTION_ID = PART1.PO_TRANSACTION_ID
AND PART1.COST_TYPE_ID = P_COST_TYPE
AND PART1.COST_GROUP_ID = P_COST_GROUP
AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART1.COST_TYPE_ID = PAWO.COST_TYPE_ID
AND PART1.COST_GROUP_ID = PAWO.COST_GROUP_ID
AND PART1.LEGAL_ENTITY_ID = PAWO.LEGAL_ENTITY_ID );
,'update_po() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP part
SET
(WRITE_OFF_ID,WRITE_OFF_FLAG) = (SELECT
MAX(WRITE_OFF_ID),
DECODE(SIGN(SUM(DECODE(WRITE_OFF_CODE
,'WRITE OFF'
,1
,-1)))
,1
,'Y'
,'N')
FROM
CST_PAC_ACCRUAL_WRITE_OFFS PAWO
WHERE PAWO.INVOICE_ID = PART.INVOICE_ID
AND PAWO.INVOICE_LINE_NUM = PART.INVOICE_LINE_NUM
AND PAWO.COST_TYPE_ID = P_COST_TYPE
AND PAWO.COST_GROUP_ID = P_COST_GROUP
AND PAWO.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
GROUP BY
PAWO.INVOICE_ID,
PAWO.INVOICE_LINE_NUM)
WHERE PART.COST_TYPE_ID = P_COST_TYPE
AND PART.COST_GROUP_ID = P_COST_GROUP
AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART.ROWID in (
SELECT
PART1.ROWID
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP PART1,
CST_PAC_ACCRUAL_WRITE_OFFS PAWO
WHERE PAWO.INVOICE_ID = PART1.INVOICE_ID
AND PAWO.INVOICE_LINE_NUM = PART1.INVOICE_LINE_NUM
AND PART1.COST_TYPE_ID = P_COST_TYPE
AND PART1.COST_GROUP_ID = P_COST_GROUP
AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART1.COST_TYPE_ID = PAWO.COST_TYPE_ID
AND PART1.COST_GROUP_ID = PAWO.COST_GROUP_ID
AND PART1.LEGAL_ENTITY_ID = PAWO.LEGAL_ENTITY_ID );
,'update_writeoff() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
,'update_writeoff() >X ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
END UPDATE_WRITEOFF;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP part
SET
(AGING_DATE,AVG_RECEIPT_PRICE) = (SELECT
MIN(PART1.TRANSACTION_DATE),
DECODE(SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_QUANTITY
,0))
,0
,0
,(-1) * SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_AMOUNT
,0)) / SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_QUANTITY
,0)))
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
WHERE PART1.PO_LINE_ID = PART.PO_LINE_ID
AND PART1.LINE_LOCATION_ID = PART.LINE_LOCATION_ID
AND PART1.ACCRUAL_ACCOUNT_ID = PART.ACCRUAL_ACCOUNT_ID
AND PART1.WRITE_OFF_FLAG = 'N'
AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
,PART1.TRANSACTION_DATE - 1)
AND NVL(P_GL_DATE_TO
,PART1.TRANSACTION_DATE + 1)
AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART1.COST_TYPE_ID = P_COST_TYPE
AND PART1.COST_GROUP_ID = P_COST_GROUP)
WHERE PART.WRITE_OFF_FLAG = 'N'
AND PART.PO_LINE_ID is NOT NULL
AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART.COST_TYPE_ID = P_COST_TYPE
AND PART.COST_GROUP_ID = P_COST_GROUP;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP part
SET
(NET_PO_LINE_QUANTITY,NET_PO_LINE_AMOUNT) = (SELECT
SUM(PART1.TRANSACTION_QUANTITY),
SUM(PART1.TRANSACTION_AMOUNT)
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
WHERE PART1.PO_LINE_ID = PART.PO_LINE_ID
AND PART1.ACCRUAL_ACCOUNT_ID = PART.ACCRUAL_ACCOUNT_ID
AND PART1.WRITE_OFF_FLAG = 'N'
AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
,PART1.TRANSACTION_DATE - 1)
AND NVL(P_GL_DATE_TO
,PART1.TRANSACTION_DATE + 1)
AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART1.COST_TYPE_ID = P_COST_TYPE
AND PART1.COST_GROUP_ID = P_COST_GROUP)
WHERE PART.WRITE_OFF_FLAG = 'N'
AND PART.PO_LINE_ID is NOT NULL
AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART.COST_TYPE_ID = P_COST_TYPE
AND PART.COST_GROUP_ID = P_COST_GROUP;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP part
SET
(AGING_DATE,AVG_RECEIPT_PRICE,NET_PO_LINE_QUANTITY,NET_PO_LINE_AMOUNT) = (SELECT
PART1.TRANSACTION_DATE,
DECODE(SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_QUANTITY
,0))
,0
,0
,(-1) * SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_AMOUNT
,0)) / SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_QUANTITY
,0))),
PART1.TRANSACTION_QUANTITY,
PART1.TRANSACTION_AMOUNT
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
WHERE PART1.ROWID = PART.ROWID
AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
,PART1.TRANSACTION_DATE - 1)
AND NVL(P_GL_DATE_TO
,PART1.TRANSACTION_DATE + 1)
AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART1.COST_TYPE_ID = P_COST_TYPE
AND PART1.COST_GROUP_ID = P_COST_GROUP
GROUP BY
PART1.TRANSACTION_DATE,
PART1.TRANSACTION_QUANTITY,
PART1.TRANSACTION_AMOUNT)
WHERE PART.WRITE_OFF_FLAG = 'N'
AND PART.PO_LINE_ID is NULL
AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART.COST_TYPE_ID = P_COST_TYPE
AND PART.COST_GROUP_ID = P_COST_GROUP;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP part
SET
(AGING_DATE,AVG_RECEIPT_PRICE) = (SELECT
MIN(PART1.TRANSACTION_DATE),
DECODE(SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_QUANTITY
,0))
,0
,0
,(-1) * SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_AMOUNT
,0)) / SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_QUANTITY
,0)))
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
WHERE PART1.PO_LINE_ID = PART.PO_LINE_ID
AND PART1.LINE_LOCATION_ID = PART.LINE_LOCATION_ID
AND PART1.ACCRUAL_ACCOUNT_ID = PART.ACCRUAL_ACCOUNT_ID
AND PART1.WRITE_OFF_FLAG = 'Y'
AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
,PART1.TRANSACTION_DATE - 1)
AND NVL(P_GL_DATE_TO
,PART1.TRANSACTION_DATE + 1)
AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART1.COST_TYPE_ID = P_COST_TYPE
AND PART1.COST_GROUP_ID = P_COST_GROUP)
WHERE PART.WRITE_OFF_FLAG = 'Y'
AND PART.PO_LINE_ID is NOT NULL
AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART.COST_TYPE_ID = P_COST_TYPE
AND PART.COST_GROUP_ID = P_COST_GROUP;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP part
SET
(NET_PO_LINE_QUANTITY,NET_PO_LINE_AMOUNT) = (SELECT
SUM(PART1.TRANSACTION_QUANTITY),
0
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
WHERE PART1.PO_LINE_ID = PART.PO_LINE_ID
AND PART1.ACCRUAL_ACCOUNT_ID = PART.ACCRUAL_ACCOUNT_ID
AND PART1.WRITE_OFF_FLAG = 'Y'
AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
,PART1.TRANSACTION_DATE - 1)
AND NVL(P_GL_DATE_TO
,PART1.TRANSACTION_DATE + 1)
AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART1.COST_TYPE_ID = P_COST_TYPE
AND PART1.COST_GROUP_ID = P_COST_GROUP)
WHERE PART.WRITE_OFF_FLAG = 'Y'
AND PART.PO_LINE_ID is NOT NULL
AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART.COST_TYPE_ID = P_COST_TYPE
AND PART.COST_GROUP_ID = P_COST_GROUP;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP part
SET
(AGING_DATE,AVG_RECEIPT_PRICE,NET_PO_LINE_QUANTITY,NET_PO_LINE_AMOUNT) = (SELECT
PART1.TRANSACTION_DATE,
DECODE(SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_QUANTITY
,0))
,0
,0
,(-1) * SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_AMOUNT
,0)) / SUM(DECODE(INVOICE_ID
,NULL
,PART1.TRANSACTION_QUANTITY
,0))),
PART1.TRANSACTION_QUANTITY,
0
FROM
CST_PAC_ACCRUAL_RECONCILE_TEMP PART1
WHERE PART1.ROWID = PART.ROWID
AND PART1.TRANSACTION_DATE between NVL(P_GL_DATE_FROM
,PART1.TRANSACTION_DATE - 1)
AND NVL(P_GL_DATE_TO
,PART1.TRANSACTION_DATE + 1)
AND PART1.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART1.COST_TYPE_ID = P_COST_TYPE
AND PART1.COST_GROUP_ID = P_COST_GROUP
GROUP BY
PART1.TRANSACTION_DATE,
PART1.TRANSACTION_QUANTITY)
WHERE PART.WRITE_OFF_FLAG = 'Y'
AND PART.PO_LINE_ID is NULL
AND PART.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND PART.COST_TYPE_ID = P_COST_TYPE
AND PART.COST_GROUP_ID = P_COST_GROUP;
PROCEDURE INSERT_MFG_DATA IS
WIP_IS_INSTALLED VARCHAR2(1);
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
sysdate,
NULL,
P_ORG_ID,
P_MASTER_ORG_ID,
CAL.CODE_COMBINATION_ID,
PLC.DISPLAYED_FIELD,
NULL,
CAH.ACCOUNTING_DATE,
NULL,
RSH.RECEIPT_NUM,
NULL,
CAH.ACCOUNTING_EVENT_ID,
MMT.TRANSACTION_TYPE_ID,
NULL,
NULL,
MMT.INVENTORY_ITEM_ID,
POL.UNIT_MEAS_LOOKUP_CODE,
NULL,
DECODE(RCT.PRIMARY_QUANTITY
,0
,0
,(-1) * MMT.PRIMARY_QUANTITY * NVL(RCT.SOURCE_DOC_QUANTITY / RCT.PRIMARY_QUANTITY
,1)),
NULL,
MMT.TRANSACTION_SOURCE_ID,
POH.SEGMENT1,
POL.LINE_NUM,
NVL(MMT.TRX_SOURCE_LINE_ID
,RCT.PO_LINE_ID),
RCT.PO_DISTRIBUTION_ID,
POH.VENDOR_ID,
POV.VENDOR_NAME,
POV.VENDOR_NAME_ALT,
DECODE(RCT.SOURCE_DOC_QUANTITY * MMT.PRIMARY_QUANTITY
,0
,0
,(-1) * ROUND((NVL(CAL.ACCOUNTED_DR
,(-1) * CAL.ACCOUNTED_CR) / MMT.PRIMARY_QUANTITY) * NVL(RCT.PRIMARY_QUANTITY / RCT.SOURCE_DOC_QUANTITY
,1)
,P_EXT_PREC)),
NULL,
NULL,
NULL,
ROUND(NVL(CAL.ACCOUNTED_DR
,(-1) * CAL.ACCOUNTED_CR) / ROUND_UNIT) * ROUND_UNIT,
NULL,
'INV',
'N',
NULL,
RCT.DESTINATION_TYPE_CODE,
NULL,
NULL,
RCT.PO_LINE_LOCATION_ID,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
PO_LOOKUP_CODES PLC,
PO_VENDORS POV,
PO_LINES_ALL POL,
PO_HEADERS_ALL POH,
RCV_SHIPMENT_HEADERS RSH,
RCV_TRANSACTIONS RCT,
MTL_MATERIAL_TRANSACTIONS MMT,
CST_AE_HEADERS CAH,
CST_AE_LINES CAL,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
WHERE CAA.REQUEST_ID = P_CONC_REQUEST_ID
AND CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
AND CAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
AND CAH.GL_TRANSFER_FLAG = 'Y'
AND CAH.COST_TYPE_ID = P_COST_TYPE
AND CAH.COST_GROUP_ID = P_COST_GROUP
AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND MMT.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
AND RCT.TRANSACTION_ID = MMT.RCV_TRANSACTION_ID
AND NVL(RCT.ORGANIZATION_ID
,P_ORG_ID) = P_ORG_ID
AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
AND POH.PO_HEADER_ID = RCT.PO_HEADER_ID
AND POH.ORG_ID = P_OPERATING_UNIT
AND POL.PO_LINE_ID = RCT.PO_LINE_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
,POV.VENDOR_NAME)
AND NVL(P_VENDOR_TO
,POV.VENDOR_NAME)
AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
AND PLC.LOOKUP_CODE = RCT.TRANSACTION_TYPE
AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
/*Support for LCM*/
AND NOT ( mmt.transaction_action_id = 24
AND nvl(mmt.source_code,'XXX') = 'PACLCMADJ'
);
,'insert_inv_data() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
SYSDATE,
NULL,
P_ORG_ID,
P_MASTER_ORG_ID,
CAL.CODE_COMBINATION_ID,
PLC.DISPLAYED_FIELD,
NULL,
CAH.ACCOUNTING_DATE,
NULL,
RSH.RECEIPT_NUM,
NULL,
NULL,
NULL,
WTS.TRANSACTION_ID,
WTS.TRANSACTION_TYPE,
POL.ITEM_ID,
POL.UNIT_MEAS_LOOKUP_CODE,
RCT.UNIT_OF_MEASURE,
DECODE(RCT.PRIMARY_QUANTITY
,0
,0
,(-1) * NVL(WTS.PRIMARY_QUANTITY
,0) * NVL(RCT.SOURCE_DOC_QUANTITY / RCT.PRIMARY_QUANTITY
,1)),
NULL,
WTS.PO_HEADER_ID,
POH.SEGMENT1,
POL.LINE_NUM,
WTS.PO_LINE_ID,
RCT.PO_DISTRIBUTION_ID,
POH.VENDOR_ID,
POV.VENDOR_NAME,
POV.VENDOR_NAME_ALT,
DECODE(RCT.SOURCE_DOC_QUANTITY * WTS.PRIMARY_QUANTITY
,0
,0
,(-1) * ROUND((NVL(CAL.ACCOUNTED_DR
,(-1) * CAL.ACCOUNTED_CR) / WTS.PRIMARY_QUANTITY) * NVL(RCT.PRIMARY_QUANTITY / RCT.SOURCE_DOC_QUANTITY
,1)
,P_EXT_PREC)),
NULL,
NULL,
NULL,
ROUND(NVL(CAL.ACCOUNTED_DR
,(-1) * CAL.ACCOUNTED_CR) / ROUND_UNIT) * ROUND_UNIT,
NULL,
'WIP',
'N',
NULL,
RCT.DESTINATION_TYPE_CODE,
NULL,
NULL,
RCT.PO_LINE_LOCATION_ID,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_VENDORS POV,
PO_LOOKUP_CODES PLC,
RCV_TRANSACTIONS RCT,
RCV_SHIPMENT_HEADERS RSH,
CST_AE_HEADERS CAH,
CST_AE_LINES CAL,
WIP_TRANSACTIONS WTS,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
WHERE CAA.REQUEST_ID = P_CONC_REQUEST_ID
AND CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
AND CAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
AND CAH.GL_TRANSFER_FLAG = 'Y'
AND CAH.COST_TYPE_ID = P_COST_TYPE
AND CAH.COST_GROUP_ID = P_COST_GROUP
AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND WTS.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
AND WTS.TRANSACTION_TYPE = 3
AND RCT.TRANSACTION_ID = WTS.RCV_TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
AND RCT.ORGANIZATION_ID = P_ORG_ID
AND POH.PO_HEADER_ID = RCT.PO_HEADER_ID
AND POH.ORG_ID = P_OPERATING_UNIT
AND POL.PO_LINE_ID = RCT.PO_LINE_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND POV.VENDOR_NAME BETWEEN NVL(P_VENDOR_FROM
,POV.VENDOR_NAME)
AND NVL(P_VENDOR_TO
,POV.VENDOR_NAME)
AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
AND PLC.LOOKUP_CODE = RCT.TRANSACTION_TYPE
AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
,'insert_wip_data() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
,'insert_mfg_static() >X ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
END INSERT_MFG_DATA;
SELECT /*+ ORDERED */ -- Added for perf bug rgangara */
DISTINCT
POH.ORG_ID
FROM
CST_COST_GROUP_ASSIGNMENTS CCGA,
RCV_TRANSACTIONS RT,
PO_HEADERS_ALL POH
WHERE POH.PO_HEADER_ID = RT.PO_HEADER_ID
AND CCGA.ORGANIZATION_ID = RT.ORGANIZATION_ID
AND CCGA.COST_GROUP_ID = P_COST_GROUP;
SELECT
MP.ORGANIZATION_ID,
MP.MASTER_ORGANIZATION_ID
FROM
MTL_PARAMETERS MP,
CST_COST_GROUP_ASSIGNMENTS CGA
WHERE MP.ORGANIZATION_ID = CGA.ORGANIZATION_ID
AND CGA.COST_GROUP_ID = P_COST_GROUP;
SELECT
SET_OF_BOOKS_ID
INTO P_SET_OF_BOOKS_ID
FROM
CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = P_LEGAL_ENTITY
AND COST_TYPE_ID = P_COST_TYPE;
SELECT
GLL.LEDGER_NAME,
GLL.CURRENCY_CODE,
GLL.CHART_OF_ACCOUNTS_ID,
NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
,POWER(10
,NVL(-FC.PRECISION
,0))),
NVL(FC.EXTENDED_PRECISION
,NVL(FC.PRECISION
,0)),
PLC1.DISPLAYED_FIELD,
PLC2.DISPLAYED_FIELD
INTO P_COMPANY,P_CURRENCY_CODE,LP_CHART_OF_ACCOUNTS_ID,ROUND_UNIT,P_EXT_PREC,P_SORT_BY,P_ITEM_VENDOR
FROM
GL_LEDGER_LE_V GLL,
FND_CURRENCIES FC,
PO_LOOKUP_CODES PLC1,
PO_LOOKUP_CODES PLC2
WHERE GLL.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND GLL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND GLL.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND GLL.CURRENCY_CODE = FC.CURRENCY_CODE
AND PLC1.LOOKUP_TYPE = 'SRS ORDER BY'
AND PLC1.LOOKUP_CODE = P_SORT_OPTION
AND PLC2.LOOKUP_TYPE = 'SRS ORDER BY'
AND PLC2.LOOKUP_CODE = DECODE(P_SORT_OPTION
,'ITEM'
,'VENDOR'
,'ITEM');
SELECT
PROGRAM_APPLICATION_ID,
CONCURRENT_PROGRAM_ID
INTO P_APPL_ID,P_PROGRAM_ID
FROM
FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = P_CONC_REQUEST_ID;
SELECT
APPLICATION_ID,
CONCURRENT_PROGRAM_ID
INTO P_APPL_ID,P_PROGRAM_ID
FROM
FND_CONCURRENT_PROGRAMS
WHERE APPLICATION_ID = 702
AND CONCURRENT_PROGRAM_NAME = 'BOM_CSTRACCR_XMLP_PKG';
DELETE_TABLE;
,'Deleted Tables')*/NULL;
SELECT
INVENTORY_ORGANIZATION_ID
INTO P_MASTER_ORG_ID
FROM
FINANCIALS_SYSTEM_PARAMS_ALL
WHERE NVL(ORG_ID
,P_OPERATING_UNIT) = P_OPERATING_UNIT;
SELECT
count(*)
INTO L_COUNT
FROM
CST_COST_GROUP_ASSIGNMENTS
WHERE COST_GROUP_ID = P_COST_GROUP
AND ORGANIZATION_ID = P_MASTER_ORG_ID;
INSERT_AP_MISC;
INSERT_MISC_MFG_DATA;
,'Inserted misc mfg data')*/NULL;
UPDATE_WRITEOFF;
,'Updated write offs')*/NULL;
SELECT
PERIOD_START_DATE,
PERIOD_END_DATE,
PERIOD_NAME
INTO P_GL_DATE_FROM,P_GL_DATE_TO,P_PERIOD_NAME
FROM
CST_PAC_PERIODS
WHERE PAC_PERIOD_ID = P_PERIOD;
SELECT
COST_GROUP
INTO P_COST_GROUP_NAME
FROM
CST_COST_GROUPS
WHERE COST_GROUP_ID = P_COST_GROUP;
SELECT
COST_TYPE
INTO P_COST_TYPE_NAME
FROM
CST_COST_TYPES
WHERE COST_TYPE_ID = P_COST_TYPE;
SELECT
NAME
INTO P_LEGAL_ENTITY_NAME
FROM
XLE_FIRSTPARTY_INFORMATION_V
WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY;
UPDATE
CST_PAC_ACCRUAL_RECONCILE_TEMP
SET
ACCRUAL_CODE = (SELECT
DISPLAYED_FIELD
FROM
PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'ACCRUAL TYPE'
AND LOOKUP_CODE = 'AP RECEIPT MATCH')
WHERE ACCRUAL_CODE = 'AP: not yet processed'
AND RECEIPT_NUM IS NOT NULL
AND LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND COST_TYPE_ID = P_COST_TYPE
AND COST_GROUP_ID = P_COST_GROUP;
PROCEDURE INSERT_MISC_MFG_DATA IS
WIP_IS_INSTALLED VARCHAR2(1);
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
sysdate,
NULL,
P_ORG_ID,
P_MASTER_ORG_ID,
CAL.CODE_COMBINATION_ID,
MTT.TRANSACTION_TYPE_NAME,
NULL,
CAH.ACCOUNTING_DATE,
NULL,
NULL,
NULL,
MMT.TRANSACTION_ID,
MMT.TRANSACTION_TYPE_ID,
NULL,
NULL,
MMT.INVENTORY_ITEM_ID,
NULL,
MSI.PRIMARY_UOM_CODE,
NVL(MMT.PRIMARY_QUANTITY
,0),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
ROUND(ABS(NVL(CAL.ACCOUNTED_DR
,(-1) * CAL.ACCOUNTED_CR) / DECODE(MMT.PRIMARY_QUANTITY
,0
,1
,MMT.PRIMARY_QUANTITY))
,P_EXT_PREC),
NULL,
NULL,
NULL,
ROUND(NVL(CAL.ACCOUNTED_DR
,(-1) * CAL.ACCOUNTED_CR) / ROUND_UNIT) * ROUND_UNIT,
NULL,
'INV',
'N',
NULL,
NULL,
NULL,
NULL,
NULL,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
CST_AE_HEADERS CAH,
CST_AE_LINES CAL,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS MSI,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
WHERE CAA.REQUEST_ID = P_CONC_REQUEST_ID
AND CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
AND CAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
AND CAH.GL_TRANSFER_FLAG = 'Y'
AND CAH.COST_TYPE_ID = P_COST_TYPE
AND CAH.COST_GROUP_ID = P_COST_GROUP
AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND CAH.ORGANIZATION_ID = P_ORG_ID
AND MMT.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
AND MMT.RCV_TRANSACTION_ID is NULL
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
/*Support for LCM*/
AND NOT ( mmt.transaction_action_id = 24
AND nvl(mmt.source_code,'XXX') = 'PACLCMADJ'
);
,'insert_inv_misc() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
INSERT INTO CST_PAC_ACCRUAL_RECONCILE_TEMP
(REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,PERIOD_NAME
,TRANSACTION_ORGANIZATION_ID
,ITEM_MASTER_ORGANIZATION_ID
,ACCRUAL_ACCOUNT_ID
,ACCRUAL_CODE
,PO_TRANSACTION_TYPE
,TRANSACTION_DATE
,INVOICE_NUM
,RECEIPT_NUM
,PO_TRANSACTION_ID
,INV_TRANSACTION_ID
,INV_TRANSACTION_TYPE_ID
,WIP_TRANSACTION_ID
,WIP_TRANSACTION_TYPE_ID
,INVENTORY_ITEM_ID
,PO_UNIT_OF_MEASURE
,PRIMARY_UNIT_OF_MEASURE
,TRANSACTION_QUANTITY
,NET_PO_LINE_QUANTITY
,PO_HEADER_ID
,PO_NUM
,PO_LINE_NUM
,PO_LINE_ID
,PO_DISTRIBUTION_ID
,VENDOR_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,TRANSACTION_UNIT_PRICE
,INVOICE_ID
,INVOICE_LINE_NUM
,AVG_RECEIPT_PRICE
,TRANSACTION_AMOUNT
,LINE_MATCH_ORDER
,TRANSACTION_SOURCE_CODE
,WRITE_OFF_FLAG
,WRITE_OFF_ID
,DESTINATION_TYPE_CODE
,REASON_ID
,COMMENTS
,LINE_LOCATION_ID
,COST_TYPE_ID
,COST_GROUP_ID
,LEGAL_ENTITY_ID
,PERIOD_ID)
SELECT
P_CONC_REQUEST_ID,
P_APPL_ID,
P_PROGRAM_ID,
SYSDATE,
NULL,
P_ORG_ID,
P_MASTER_ORG_ID,
CAL.CODE_COMBINATION_ID,
MFL.MEANING,
NULL,
CAH.ACCOUNTING_DATE,
NULL,
NULL,
NULL,
NULL,
NULL,
WTS.TRANSACTION_ID,
WTS.TRANSACTION_TYPE,
NULL,
NULL,
WTS.PRIMARY_UOM,
NVL(WTS.TRANSACTION_QUANTITY
,0),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
ROUND(ABS(NVL(CAL.ACCOUNTED_DR
,(-1) * CAL.ACCOUNTED_CR) / DECODE(WTS.TRANSACTION_QUANTITY
,0
,1
,WTS.TRANSACTION_QUANTITY))
,P_EXT_PREC),
NULL,
NULL,
NULL,
ROUND(NVL(CAL.ACCOUNTED_DR
,(-1) * CAL.ACCOUNTED_CR) / ROUND_UNIT) * ROUND_UNIT,
NULL,
'WIP',
'N',
NULL,
NULL,
NULL,
NULL,
NULL,
P_COST_TYPE,
P_COST_GROUP,
P_LEGAL_ENTITY,
P_PERIOD
FROM
CST_AE_HEADERS CAH,
CST_AE_LINES CAL,
WIP_TRANSACTIONS WTS,
MFG_LOOKUPS MFL,
CST_PAC_ACCRUAL_ACCOUNTS_TEMP CAA
WHERE CAA.REQUEST_ID = P_CONC_REQUEST_ID
AND CAL.CODE_COMBINATION_ID = CAA.ACCRUAL_ACCOUNT_ID
AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
AND CAH.ACCOUNTING_DATE BETWEEN P_GL_DATE_FROM AND P_GL_DATE_TO
AND CAH.GL_TRANSFER_FLAG = 'Y'
AND CAH.COST_TYPE_ID = P_COST_TYPE
AND CAH.COST_GROUP_ID = P_COST_GROUP
AND CAH.LEGAL_ENTITY_ID = P_LEGAL_ENTITY
AND CAH.ORGANIZATION_ID = P_ORG_ID
AND WTS.TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID
AND WTS.RCV_TRANSACTION_ID is NULL
AND MFL.LOOKUP_CODE = WTS.TRANSACTION_TYPE
AND MFL.LOOKUP_TYPE = 'WIP_TRANSACTION_TYPE'
AND CAH.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
,'insert_wip_misc() >> ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
,'insert_misc_mfg_data() >X ' || TO_CHAR(SYSDATE
,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
END INSERT_MISC_MFG_DATA;