[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PJM_TASK_AUTO_ASSIGN.Inv_Task_WNPS
( POD.Destination_Organization_Id
, X_Project_Id
, POL.Item_Id
, POD.Po_Header_Id
, Null
, Null )
FROM PO_Distributions POD
, PO_Lines POL
WHERE POD.PO_Distribution_Id = X_PO_Distribution_Id
AND POL.PO_Line_Id = POD.PO_Line_Id;
SELECT PJM_TASK_AUTO_ASSIGN.WIP_Task_WNPS
( wo.organization_id
, X_Project_Id
, wo.standard_operation_id
, wdj.wip_entity_id
, wdj.primary_item_id
, wo.department_id )
FROM PO_Distributions POD
, WIP_Discrete_Jobs WDJ
, WIP_Operations WO
WHERE POD.PO_Distribution_Id = X_PO_Distribution_Id
AND WO.WIP_Entity_Id = POD.WIP_Entity_Id
AND WO.Operation_Seq_Num = POD.WIP_Operation_Seq_Num
AND WDJ.WIP_Entity_Id = WO.Wip_Entity_Id;
SELECT
INV.Invoice_id Invoice_Id
, DIST.Distribution_Line_Number Distribution_Line_Number
, DIST.Amount Amount
, DIST.Base_Amount Base_Amount
, PAP.Project_Id Project_Id
, PAP.Segment1 Project_Number
, POD.Task_id Task_Id
, DIST.Accounting_Date Expenditure_Item_Date
, INV.Vendor_Id Vendor_Id
, INV.Created_By Created_By
, POD.Destination_Organization_Id Expenditure_Organization_Id
, NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
, DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
, nvl( DIST.Accts_Pay_Code_Combination_Id
, INV.Accts_Pay_Code_Combination_Id)
Accts_Pay_Code_Combination_Id
, INV.Invoice_Currency_Code Invoice_Currency_Code
, INV.Exchange_Rate_Type Exchange_Rate_Type
, INV.Exchange_Date Exchange_Date
, INV.Exchange_Rate Exchange_Rate
, DIST.Line_Type_Lookup_Code Distribution_Type
, POD.Po_Distribution_Id Po_Distribution_Id
, POD.Destination_Type_Code Destination_Type_Code
, DIST.RCV_Transaction_Id RCV_Transaction_Id
FROM
AP_Invoices INV
, (
SELECT
AID.Invoice_Id
, AID.Distribution_Line_Number
, nvl(AID.invoice_price_variance,AID.Amount) Amount
, nvl(AID.base_invoice_price_variance,AID.Base_Amount) Base_Amount
, AID.Accounting_Date
, AID.Pa_Quantity
, AID.Dist_Code_Combination_Id
, AID.Accts_Pay_Code_Combination_Id
, AID.Line_Type_Lookup_Code
, AID.RCV_Transaction_Id
, AID.Po_Distribution_Id
FROM AP_Invoice_Distributions AID
WHERE NOT EXISTS (
SELECT 'x'
FROM AP_Invoice_Distributions AID2
WHERE AID2.Invoice_Id = AID.Invoice_ID
AND AID2.Line_Type_Lookup_Code = 'ITEM' )
AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
AND AID.Posted_Flag = 'Y'
AND AID.pa_addition_flag in
( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
, 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
AND (( l_Start_Date is null and l_End_Date is null)
OR ( l_Start_Date is not null and l_End_Date is not null
and AID.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and AID.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and AID.Accounting_Date <= l_End_Date ))
UNION ALL
SELECT
AID.Invoice_Id
, AID.Distribution_Line_Number
, nvl(AID.invoice_price_variance,ACA.Allocated_Amount) Amount
, nvl(AID.base_invoice_price_variance,ACA.Allocated_Base_Amount) Base_Amount
, AID.Accounting_Date
, AID.Pa_Quantity
, AID.Dist_Code_Combination_Id
, AID.Accts_Pay_Code_Combination_Id
, AID.Line_Type_Lookup_Code
, AID2.RCV_Transaction_Id
, AID2.Po_Distribution_Id
FROM AP_Invoice_Distributions AID
, AP_Invoice_Distributions AID2
, AP_Chrg_Allocations ACA
WHERE EXISTS (
SELECT 'x'
FROM AP_Invoice_Distributions
WHERE Invoice_Id = AID.Invoice_ID
AND Line_Type_Lookup_Code = 'ITEM' )
AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
AND AID.Posted_Flag = 'Y'
AND AID.pa_addition_flag in
( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
, 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
AND (( l_Start_Date is null and l_End_Date is null)
OR ( l_Start_Date is not null and l_End_Date is not null
and AID.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and AID.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and AID.Accounting_Date <= l_End_Date ))
AND AID.Invoice_Distribution_Id = ACA.Charge_Dist_Id
AND ACA.Item_Dist_Id = AID2.Invoice_Distribution_Id
) DIST
, PO_Distributions POD
, PA_Projects_ALL PAP
WHERE DIST.Invoice_Id = INV.Invoice_Id
AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
AND POD.Destination_Type_Code IN ( 'INVENTORY' , 'SHOP FLOOR' )
AND PAP.Project_Id = POD.Project_Id
AND PAP.Project_Id = X_Project_Id
AND DIST.Po_Distribution_Id = POD.Po_Distribution_Id
UNION ALL
SELECT
INV.Invoice_id Invoice_Id
, DIST.Distribution_Line_Number Distribution_Line_Number
, DIST.Amount Amount
, DIST.Base_Amount Base_Amount
, PAP.Project_Id Project_Id
, PAP.Segment1 Project_Number
, POD.Task_id Task_Id
, DIST.Accounting_Date Expenditure_Item_Date
, INV.Vendor_Id Vendor_Id
, INV.Created_By Created_By
, POD.Destination_Organization_Id Expenditure_Organization_Id
, NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
, DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
, nvl( DIST.Accts_Pay_Code_Combination_Id
, INV.Accts_Pay_Code_Combination_Id)
Accts_Pay_Code_Combination_Id
, INV.Invoice_Currency_Code Invoice_Currency_Code
, INV.Exchange_Rate_Type Exchange_Rate_Type
, INV.Exchange_Date Exchange_Date
, INV.Exchange_Rate Exchange_Rate
, DIST.Line_Type_Lookup_Code Distribution_Type
, POD.Po_Distribution_Id Po_Distribution_Id
, POD.Destination_Type_Code Destination_Type_Code
, DIST.RCV_Transaction_Id RCV_Transaction_Id
FROM
AP_Invoices INV
, (
SELECT
AID.Invoice_Id
, AID.Distribution_Line_Number
, nvl(AID.invoice_price_variance,AID.Amount) Amount
, nvl(AID.base_invoice_price_variance,AID.Base_Amount) Base_Amount
, AID.Accounting_Date
, AID.Pa_Quantity
, AID.Dist_Code_Combination_Id
, AID.Accts_Pay_Code_Combination_Id
, AID.Line_Type_Lookup_Code
, AID.RCV_Transaction_Id
, AID.Po_Distribution_Id
FROM AP_Invoice_Distributions AID
WHERE NOT EXISTS (
SELECT 'x'
FROM AP_Invoice_Distributions AID2
WHERE AID2.Invoice_Id = AID.Invoice_ID
AND AID2.Line_Type_Lookup_Code = 'ITEM' )
AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
AND AID.Posted_Flag = 'Y'
AND AID.pa_addition_flag in
( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
, 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
AND (( l_Start_Date is null and l_End_Date is null)
OR ( l_Start_Date is not null and l_End_Date is not null
and AID.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and AID.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and AID.Accounting_Date <= l_End_Date ))
UNION ALL
SELECT
AID.Invoice_Id
, AID.Distribution_Line_Number
, nvl(AID.invoice_price_variance,ACA.Allocated_Amount) Amount
, nvl(AID.base_invoice_price_variance,ACA.Allocated_Base_Amount) Base_Amount
, AID.Accounting_Date
, AID.Pa_Quantity
, AID.Dist_Code_Combination_Id
, AID.Accts_Pay_Code_Combination_Id
, AID.Line_Type_Lookup_Code
, AID2.RCV_Transaction_Id
, AID2.Po_Distribution_Id
FROM AP_Invoice_Distributions AID
, AP_Invoice_Distributions AID2
, AP_Chrg_Allocations ACA
WHERE EXISTS (
SELECT 'x'
FROM AP_Invoice_Distributions
WHERE Invoice_Id = AID.Invoice_ID
AND Line_Type_Lookup_Code = 'ITEM' )
AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
AND AID.Posted_Flag = 'Y'
AND AID.pa_addition_flag in
( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
, 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
AND (( l_Start_Date is null and l_End_Date is null)
OR ( l_Start_Date is not null and l_End_Date is not null
and AID.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and AID.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and AID.Accounting_Date <= l_End_Date ))
AND AID.Invoice_Distribution_Id = ACA.Charge_Dist_Id
AND ACA.Item_Dist_Id = AID2.Invoice_Distribution_Id
) DIST
, PO_Distributions POD
, PA_Projects_ALL PAP
, PJM_Org_Parameters POP
WHERE DIST.Invoice_Id = INV.Invoice_Id
AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
AND POD.Destination_Type_Code IN ( 'INVENTORY' , 'SHOP FLOOR' )
AND POP.Organization_Id = POD.Destination_Organization_Id
AND PAP.Project_Id = POP.Common_Project_Id
AND POD.Project_Id is null
AND PAP.Project_Id = X_Project_Id
AND DIST.Po_Distribution_Id = POD.Po_Distribution_Id
ORDER BY 9,1,2;
SELECT
INV.Invoice_id Invoice_Id
, DIST.Distribution_Line_Number Distribution_Line_Number
, DIST.Amount Amount
, DIST.Base_Amount Base_Amount
, PAP.Project_Id Project_Id
, PAP.Segment1 Project_Number
, POD.Task_id Task_Id
, DIST.Accounting_Date Expenditure_Item_Date
, INV.Vendor_Id Vendor_Id
, INV.Created_By Created_By
, POD.Destination_Organization_Id Expenditure_Organization_Id
, NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
, DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
, nvl( DIST.Accts_Pay_Code_Combination_Id
, INV.Accts_Pay_Code_Combination_Id)
Accts_Pay_Code_Combination_Id
, INV.Invoice_Currency_Code Invoice_Currency_Code
, INV.Exchange_Rate_Type Exchange_Rate_Type
, INV.Exchange_Date Exchange_Date
, INV.Exchange_Rate Exchange_Rate
, DIST.Line_Type_Lookup_Code Distribution_Type
, POD.Po_Distribution_Id Po_Distribution_Id
, POD.Destination_Type_Code Destination_Type_Code
, DIST.RCV_Transaction_Id RCV_Transaction_Id
FROM
AP_Invoices INV
, (
SELECT
AID.Invoice_Id
, AID.Distribution_Line_Number
, nvl(AID.invoice_price_variance,AID.Amount) Amount
, nvl(AID.base_invoice_price_variance,AID.Base_Amount) Base_Amount
, AID.Accounting_Date
, AID.Pa_Quantity
, AID.Dist_Code_Combination_Id
, AID.Accts_Pay_Code_Combination_Id
, AID.Line_Type_Lookup_Code
, AID.RCV_Transaction_Id
, AID.Po_Distribution_Id
FROM AP_Invoice_Distributions AID
WHERE NOT EXISTS (
SELECT 'x'
FROM AP_Invoice_Distributions AID2
WHERE AID2.Invoice_Id = AID.Invoice_ID
AND AID2.Line_Type_Lookup_Code = 'ITEM' )
AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
AND AID.Posted_Flag = 'Y'
AND AID.pa_addition_flag in
( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
, 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
AND (( l_Start_Date is null and l_End_Date is null)
OR ( l_Start_Date is not null and l_End_Date is not null
and AID.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and AID.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and AID.Accounting_Date <= l_End_Date ))
UNION ALL
SELECT
AID.Invoice_Id
, AID.Distribution_Line_Number
, nvl(AID.invoice_price_variance,ACA.Allocated_Amount) Amount
, nvl(AID.base_invoice_price_variance,ACA.Allocated_Base_Amount) Base_Amount
, AID.Accounting_Date
, AID.Pa_Quantity
, AID.Dist_Code_Combination_Id
, AID.Accts_Pay_Code_Combination_Id
, AID.Line_Type_Lookup_Code
, AID2.RCV_Transaction_Id
, AID2.Po_Distribution_Id
FROM AP_Invoice_Distributions AID
, AP_Invoice_Distributions AID2
, AP_Chrg_Allocations ACA
WHERE EXISTS (
SELECT 'x'
FROM AP_Invoice_Distributions
WHERE Invoice_Id = AID.Invoice_ID
AND Line_Type_Lookup_Code = 'ITEM' )
AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
AND AID.Posted_Flag = 'Y'
AND AID.pa_addition_flag in
( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
, 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
AND (( l_Start_Date is null and l_End_Date is null)
OR ( l_Start_Date is not null and l_End_Date is not null
and AID.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and AID.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and AID.Accounting_Date <= l_End_Date ))
AND AID.Invoice_Distribution_Id = ACA.Charge_Dist_Id
AND ACA.Item_Dist_Id = AID2.Invoice_Distribution_Id
) DIST
, PO_Distributions POD
, PA_Projects_ALL PAP
WHERE DIST.Invoice_Id = INV.Invoice_Id
AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
AND POD.Destination_Type_Code IN ( 'INVENTORY' , 'SHOP FLOOR' )
AND PAP.Project_Id = POD.Project_Id
AND DIST.Po_Distribution_Id = POD.Po_Distribution_Id
UNION ALL
SELECT
INV.Invoice_id Invoice_Id
, DIST.Distribution_Line_Number Distribution_Line_Number
, DIST.Amount Amount
, DIST.Base_Amount Base_Amount
, PAP.Project_Id Project_Id
, PAP.Segment1 Project_Number
, POD.Task_id Task_Id
, DIST.Accounting_Date Expenditure_Item_Date
, INV.Vendor_Id Vendor_Id
, INV.Created_By Created_By
, POD.Destination_Organization_Id Expenditure_Organization_Id
, NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
, DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
, nvl( DIST.Accts_Pay_Code_Combination_Id
, INV.Accts_Pay_Code_Combination_Id)
Accts_Pay_Code_Combination_Id
, INV.Invoice_Currency_Code Invoice_Currency_Code
, INV.Exchange_Rate_Type Exchange_Rate_Type
, INV.Exchange_Date Exchange_Date
, INV.Exchange_Rate Exchange_Rate
, DIST.Line_Type_Lookup_Code Distribution_Type
, POD.Po_Distribution_Id Po_Distribution_Id
, POD.Destination_Type_Code Destination_Type_Code
, DIST.RCV_Transaction_Id RCV_Transaction_Id
FROM
AP_Invoices INV
, (
SELECT
AID.Invoice_Id
, AID.Distribution_Line_Number
, nvl(AID.invoice_price_variance,AID.Amount) Amount
, nvl(AID.base_invoice_price_variance,AID.Base_Amount) Base_Amount
, AID.Accounting_Date
, AID.Pa_Quantity
, AID.Dist_Code_Combination_Id
, AID.Accts_Pay_Code_Combination_Id
, AID.Line_Type_Lookup_Code
, AID.RCV_Transaction_Id
, AID.Po_Distribution_Id
FROM AP_Invoice_Distributions AID
WHERE NOT EXISTS (
SELECT 'x'
FROM AP_Invoice_Distributions AID2
WHERE AID2.Invoice_Id = AID.Invoice_ID
AND AID2.Line_Type_Lookup_Code = 'ITEM' )
AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
AND AID.Posted_Flag = 'Y'
AND AID.pa_addition_flag in
( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
, 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
AND (( l_Start_Date is null and l_End_Date is null)
OR ( l_Start_Date is not null and l_End_Date is not null
and AID.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and AID.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and AID.Accounting_Date <= l_End_Date ))
UNION ALL
SELECT
AID.Invoice_Id
, AID.Distribution_Line_Number
, nvl(AID.invoice_price_variance,ACA.Allocated_Amount) Amount
, nvl(AID.base_invoice_price_variance,ACA.Allocated_Base_Amount) Base_Amount
, AID.Accounting_Date
, AID.Pa_Quantity
, AID.Dist_Code_Combination_Id
, AID.Accts_Pay_Code_Combination_Id
, AID.Line_Type_Lookup_Code
, AID2.RCV_Transaction_Id
, AID2.Po_Distribution_Id
FROM AP_Invoice_Distributions AID
, AP_Invoice_Distributions AID2
, AP_Chrg_Allocations ACA
WHERE EXISTS (
SELECT 'x'
FROM AP_Invoice_Distributions
WHERE Invoice_Id = AID.Invoice_ID
AND Line_Type_Lookup_Code = 'ITEM' )
AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
AND AID.Posted_Flag = 'Y'
AND AID.pa_addition_flag in
( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
, 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
AND (( l_Start_Date is null and l_End_Date is null)
OR ( l_Start_Date is not null and l_End_Date is not null
and AID.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and AID.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and AID.Accounting_Date <= l_End_Date ))
AND AID.Invoice_Distribution_Id = ACA.Charge_Dist_Id
AND ACA.Item_Dist_Id = AID2.Invoice_Distribution_Id
) DIST
, PO_Distributions POD
, PA_Projects_ALL PAP
, PJM_Org_Parameters POP
WHERE DIST.Invoice_Id = INV.Invoice_Id
AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
AND POD.Destination_Type_Code IN ( 'INVENTORY' , 'SHOP FLOOR' )
AND POP.Organization_Id = POD.Destination_Organization_Id
AND PAP.Project_Id = POP.Common_Project_Id
AND POD.Project_Id is null
AND DIST.Po_Distribution_Id = POD.Po_Distribution_Id
ORDER BY 9,1,2;
select ap.base_currency_code
into l_base_currency_code
from gl_sets_of_books gl
, ap_system_parameters ap
where gl.set_of_books_id = ap.set_of_books_id;
select decode(InvRec.Distribution_Type,
'FREIGHT', nvl(ppp.freight_expenditure_type,
pop.freight_expenditure_type),
'TAX', nvl(ppp.tax_expenditure_type,
pop.tax_expenditure_type),
'MISC', nvl(ppp.misc_expenditure_type,
pop.misc_expenditure_type),
nvl(ppp.misc_expenditure_type,
pop.misc_expenditure_type))
into l_expenditure_type
from pjm_project_parameters ppp
, pjm_org_parameters pop
where pop.organization_id = InvRec.Expenditure_Organization_Id
and ppp.organization_id (+) = pop.organization_id
and ppp.project_id (+) = InvRec.Project_Id;
UPDATE AP_Invoice_distributions DIST
SET DIST.PA_Addition_Flag =
DECODE(l_proj_status, 'PA_EX_PROJECT_CLOSED', 'P',
'PA_EX_PROJECT_DATE', 'D',
'PA_EXP_TASK_STATUS', 'C',
'PA_EXP_TASK_EFF', 'I',
'PA_EXP_PJ_TC', 'J',
'PA_EXP_TASK_TC', 'K',
'PA_EXP_INV_PJTK', 'M',
NULL, 'S',
'Q')
, DIST.Last_Update_Date = SYSDATE
, DIST.Last_Updated_By = l_user_id
, DIST.Request_Id = l_request_id
WHERE
DIST.Invoice_Id = InvRec.Invoice_Id
AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number;
SELECT rsh.receipt_num
INTO l_receipt_num
FROM rcv_shipment_headers rsh
, rcv_transactions rt
WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
AND rsh.shipment_header_id = rt.shipment_header_id;
SELECT User_Conversion_Type
INTO l_User_Conv_Type
FROM gl_daily_conversion_types
WHERE conversion_type = InvRec.Exchange_Rate_Type;
fnd_message.set_name('PJM','CONC-APINV Insert');
select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled_flag,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = InvRec.Expenditure_Organization_Id;
INSERT INTO pa_transaction_interface
(transaction_source,
batch_name,
expenditure_ending_date,
employee_number,
organization_name,
expenditure_item_date,
project_number,
task_number,
expenditure_type,
quantity,
expenditure_comment,
orig_transaction_reference,
unmatched_negative_txn_flag,
dr_code_combination_id,
cr_code_combination_id,
orig_exp_txn_reference1,
orig_exp_txn_reference2,
orig_exp_txn_reference3,
gl_date,
system_linkage,
transaction_status_code,
denom_currency_code,
denom_raw_cost,
denom_burdened_cost,
acct_rate_date,
acct_rate_type,
acct_exchange_rate,
acct_raw_cost,
acct_burdened_cost,
creation_date,
created_by,
last_update_date,
last_updated_by
)
SELECT
l_transaction_source
, l_Batch_Name
, pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
, NULL
, ORG.Name
, InvRec.Expenditure_Item_Date
, InvRec.Project_Number
, TASK.Task_Number
, l_Expenditure_Type
, InvRec.PA_Quantity
, decode(InvRec.Distribution_Type,
'FREIGHT', l_Freight_Exp_Comment,
'TAX', l_Tax_Exp_Comment,
'MISC', l_Misc_Exp_Comment,
l_Misc_Exp_Comment)
, DIST.Invoice_Distribution_Id
, 'Y'
, InvRec.Dist_Code_Combination_Id
, InvRec.Accts_Pay_Code_Combination_Id
, InvRec.PO_Distribution_Id
, InvRec.RCV_Transaction_Id
, l_receipt_num
, DIST.Accounting_Date
, 'INV'
, l_trx_status_code
, InvRec.Invoice_Currency_Code /* denom_currency_code */
, InvRec.Amount /* denom_raw_cost */
, InvRec.Amount /* denom_burdened_cost */
, InvRec.Exchange_Date /* acct_rate_date */
, l_User_Conv_Type /* acct_rate_type */
, InvRec.Exchange_Rate /* acct_exchange_rate */
, nvl(InvRec.Base_Amount, InvRec.Amount) /* acct_raw_cost */
, nvl(InvRec.Base_Amount, InvRec.Amount) /* acct_burdened_cost */
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
FROM
AP_Invoice_Distributions DIST
, AP_Invoices INV
, PA_Tasks TASK
, HR_Organization_Units ORG
WHERE
DIST.Invoice_Id = InvRec.Invoice_Id
AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number
AND DIST.PA_Addition_Flag = 'S'
AND INV.Invoice_ID = DIST.Invoice_Id
AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
AND TASK.Task_Id = l_Task_Id;
UPDATE AP_Invoice_Distributions
SET Pa_Addition_Flag = 'Y'
WHERE Pa_Addition_Flag = 'S'
AND Invoice_Id = InvRec.Invoice_Id
AND Distribution_Line_Number = InvRec.Distribution_Line_Number;
UPDATE AP_Invoice_Distributions
SET Pa_Addition_Flag = 'Y'
, Request_ID = l_request_id
WHERE Invoice_Id = InvRec.Invoice_Id
AND Distribution_Line_Number = InvRec.Distribution_Line_Number;
select decode(InvRec.Distribution_Type,
'FREIGHT', nvl(ppp.freight_expenditure_type,
pop.freight_expenditure_type),
'TAX', nvl(ppp.tax_expenditure_type,
pop.tax_expenditure_type),
'MISC', nvl(ppp.misc_expenditure_type,
pop.misc_expenditure_type),
nvl(ppp.misc_expenditure_type,
pop.misc_expenditure_type))
into l_expenditure_type
from pjm_project_parameters ppp
, pjm_org_parameters pop
where pop.organization_id = InvRec.Expenditure_Organization_Id
and ppp.organization_id (+) = pop.organization_id
and ppp.project_id (+) = InvRec.Project_Id;
UPDATE AP_Invoice_distributions DIST
SET DIST.PA_Addition_Flag =
DECODE(l_proj_status, 'PA_EX_PROJECT_CLOSED', 'P',
'PA_EX_PROJECT_DATE', 'D',
'PA_EXP_TASK_STATUS', 'C',
'PA_EXP_TASK_EFF', 'I',
'PA_EXP_PJ_TC', 'J',
'PA_EXP_TASK_TC', 'K',
'PA_EXP_INV_PJTK', 'M',
NULL, 'S',
'Q')
, DIST.Last_Update_Date = SYSDATE
, DIST.Last_Updated_By = l_user_id
, DIST.Request_Id = l_request_id
WHERE
DIST.Invoice_Id = InvRec.Invoice_Id
AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number;
SELECT rsh.receipt_num
INTO l_receipt_num
FROM rcv_shipment_headers rsh
, rcv_transactions rt
WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
AND rsh.shipment_header_id = rt.shipment_header_id;
SELECT User_Conversion_Type
INTO l_User_Conv_Type
FROM gl_daily_conversion_types
WHERE conversion_type = InvRec.Exchange_Rate_Type;
fnd_message.set_name('PJM','CONC-APINV Insert');
select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled_flag,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = InvRec.Expenditure_Organization_Id;
INSERT INTO pa_transaction_interface
(transaction_source,
batch_name,
expenditure_ending_date,
employee_number,
organization_name,
expenditure_item_date,
project_number,
task_number,
expenditure_type,
quantity,
expenditure_comment,
orig_transaction_reference,
unmatched_negative_txn_flag,
dr_code_combination_id,
cr_code_combination_id,
orig_exp_txn_reference1,
orig_exp_txn_reference2,
orig_exp_txn_reference3,
gl_date,
system_linkage,
transaction_status_code,
denom_currency_code,
denom_raw_cost,
denom_burdened_cost,
acct_rate_date,
acct_rate_type,
acct_exchange_rate,
acct_raw_cost,
acct_burdened_cost,
creation_date,
created_by,
last_update_date,
last_updated_by
)
SELECT
l_transaction_source
, l_Batch_Name
, pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
, NULL
, ORG.Name
, InvRec.Expenditure_Item_Date
, InvRec.Project_Number
, TASK.Task_Number
, l_Expenditure_Type
, InvRec.PA_Quantity
, decode(InvRec.Distribution_Type,
'FREIGHT', l_Freight_Exp_Comment,
'TAX', l_Tax_Exp_Comment,
'MISC', l_Misc_Exp_Comment,
l_Misc_Exp_Comment)
, DIST.Invoice_Distribution_Id
, 'Y'
, InvRec.Dist_Code_Combination_Id
, InvRec.Accts_Pay_Code_Combination_Id
, InvRec.PO_Distribution_Id
, InvRec.RCV_Transaction_Id
, l_receipt_num
, DIST.Accounting_Date
, 'INV'
, l_trx_status_code
, InvRec.Invoice_Currency_Code /* denom_currency_code */
, InvRec.Amount /* denom_raw_cost */
, InvRec.Amount /* denom_burdened_cost */
, InvRec.Exchange_Date /* acct_rate_date */
, l_User_Conv_Type /* acct_rate_type */
, InvRec.Exchange_Rate /* acct_exchange_rate */
, nvl(InvRec.Base_Amount, InvRec.Amount) /* acct_raw_cost */
, nvl(InvRec.Base_Amount, InvRec.Amount) /* acct_burdened_cost */
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
FROM
AP_Invoice_Distributions DIST
, AP_Invoices INV
, PA_Tasks TASK
, HR_Organization_Units ORG
WHERE
DIST.Invoice_Id = InvRec.Invoice_Id
AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number
AND DIST.PA_Addition_Flag = 'S'
AND INV.Invoice_ID = DIST.Invoice_Id
AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
AND TASK.Task_Id = l_Task_Id;
UPDATE AP_Invoice_Distributions
SET Pa_Addition_Flag = 'Y'
WHERE Pa_Addition_Flag = 'S'
AND Invoice_Id = InvRec.Invoice_Id
AND Distribution_Line_Number = InvRec.Distribution_Line_Number;
UPDATE AP_Invoice_Distributions
SET Pa_Addition_Flag = 'Y'
, Request_ID = l_request_id
WHERE Invoice_Id = InvRec.Invoice_Id
AND Distribution_Line_Number = InvRec.Distribution_Line_Number;