[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(ppp.ipv_expenditure_type, pop.ipv_expenditure_type)
into l_ipv_expenditure_type
from pjm_project_parameters ppp
, pjm_org_parameters pop
where pop.organization_id = X_Org_Id
and ppp.organization_id (+) = pop.organization_id
and ppp.project_id (+) = X_Project_Id;
select nvl(ppp.erv_expenditure_type, pop.erv_expenditure_type)
into l_erv_expenditure_type
from pjm_project_parameters ppp
, pjm_org_parameters pop
where pop.organization_id = X_Org_Id
and ppp.organization_id (+) = pop.organization_id
and ppp.project_id (+) = X_Project_Id;
select decode(X_Type,
'FREIGHT', nvl(ppp.freight_expenditure_type,
pop.freight_expenditure_type),
'MISCELLANEOUS', nvl(ppp.misc_expenditure_type,
pop.misc_expenditure_type),
'TIPV', nvl(ppp.tax_expenditure_type,
pop.tax_expenditure_type),
'TERV', nvl(ppp.erv_expenditure_type,
pop.erv_expenditure_type),
'IPV', nvl(ppp.ipv_expenditure_type,
pop.ipv_expenditure_type),
'ERV', nvl(ppp.erv_expenditure_type,
pop.erv_expenditure_type),
'TRV', nvl(ppp.tax_expenditure_type,
pop.tax_expenditure_type),
'NONREC_TAX', nvl(ppp.tax_expenditure_type,
pop.tax_expenditure_type),
null)
into l_expenditure_type
from pjm_project_parameters ppp
, pjm_org_parameters pop
where pop.organization_id = X_Org_ID
and ppp.organization_id (+) = pop.organization_id
and ppp.project_id (+) = X_Project_Id;
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_All POD
, PO_Lines_All 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_All 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.Invoice_Distribution_Id Invoice_Distribution_Id
, PAP.Org_Id Proj_Org_Id
, 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
, POD.Org_Id Org_Id
, DIST.description Expenditure_Comment
, 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.Amount Charge_Amount
, DIST.Base_Amount Base_Charge_Amount
, DIST.PO_Distribution_Id PO_Distribution_Id
, DIST.RCV_Transaction_Id RCV_Transaction_Id
, DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
FROM AP_Invoices_All INV,
(SELECT Invoice_Distribution_Id
, Invoice_Id
, Project_Id
, Task_id
, Accounting_Date
, Expenditure_Organization_Id
, description
, Pa_Quantity
, Dist_Code_Combination_Id
, Accts_Pay_Code_Combination_Id
, Amount
, Base_Amount
, PO_Distribution_Id
, RCV_Transaction_Id
, Line_Type_Lookup_Code
FROM AP_Invoice_Distributions_all
WHERE LINE_TYPE_LOOKUP_CODE IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV')
AND PA_ADDITION_FLAG in ('E', 'M', 'N')
AND POSTED_FLAG = 'Y'
) DIST,
PA_Projects_ALL PAP,
PJM_Org_Parameters POP,
PO_Distributions_All POD
WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
AND DIST.Invoice_Id = INV.Invoice_Id
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 DIST.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and DIST.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and DIST.Accounting_Date <= L_End_Date ))
AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
AND DIST.po_distribution_id = POD.po_distribution_id
AND POP.Organization_Id = POD.Destination_Organization_Id
AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
ORDER BY 9,1,2
for update;
SELECT INV.Invoice_id Invoice_Id
, DIST.Invoice_Distribution_Id Invoice_Distribution_Id
, PAP.Org_Id Proj_Org_Id
, 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
, POD.Org_Id Org_Id
, DIST.description Expenditure_Comment
, 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.Amount Charge_Amount
, DIST.Base_Amount Base_Charge_Amount
, DIST.PO_Distribution_Id PO_Distribution_Id
, DIST.RCV_Transaction_Id RCV_Transaction_Id
, DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
FROM AP_Invoices_All INV,
(SELECT C.Invoice_Distribution_Id
, C.Invoice_Id
, P.Project_Id
, P.Task_Id
, C.Accounting_Date
, C.Expenditure_Organization_Id
, C.description
, C.Pa_Quantity
, C.Dist_Code_Combination_Id
, C.Accts_Pay_Code_Combination_Id
, C.Amount
, C.Base_Amount
, NVL(P.PO_Distribution_Id,(SELECT PO_Distribution_Id FROM AP_Invoice_Distributions_all P1
WHERE P1.invoice_distribution_id = P.charge_applicable_to_dist_id)) PO_Distribution_Id -- bugfix 7482789
, C.RCV_Transaction_Id
, C.Line_Type_Lookup_Code
FROM AP_Invoice_Distributions_all C, AP_Invoice_Distributions_all P
WHERE C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS','NONREC_TAX') -- bugfix 7482789
AND C.PA_ADDITION_FLAG in ('E', 'M', 'N')
AND C.POSTED_FLAG = 'Y'
AND C.charge_applicable_to_dist_id = P.invoice_distribution_id
AND (P.charge_applicable_to_dist_id IS NOT NULL OR C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS')) -- bugfix 7482789
) DIST,
PA_Projects_ALL PAP,
PJM_Org_Parameters POP,
PO_Distributions_All POD
WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
AND DIST.Invoice_Id = INV.Invoice_Id
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 DIST.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and DIST.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and DIST.Accounting_Date <= L_End_Date ))
AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
AND DIST.po_distribution_id = POD.po_distribution_id
AND POP.Organization_Id = POD.Destination_Organization_Id
AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
ORDER BY 9,1,2
for update;
SELECT INV.Invoice_id Invoice_Id
, DIST.Invoice_Distribution_Id Invoice_Distribution_Id
, PAP.Org_Id Proj_Org_Id
, 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
, POD.Org_Id Org_Id
, DIST.description Expenditure_Comment
, 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.Amount Charge_Amount
, DIST.Base_Amount Base_Charge_Amount
, DIST.PO_Distribution_Id PO_Distribution_Id
, DIST.RCV_Transaction_Id RCV_Transaction_Id
, DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
FROM AP_Invoices_All INV,
(SELECT Invoice_Distribution_Id
, Invoice_Id
, Project_Id
, Task_id
, Accounting_Date
, Expenditure_Organization_Id
, description
, Pa_Quantity
, Dist_Code_Combination_Id
, Accts_Pay_Code_Combination_Id
, Amount
, Base_Amount
, PO_Distribution_Id
, RCV_Transaction_Id
, Line_Type_Lookup_Code
FROM AP_Invoice_Distributions_all
WHERE LINE_TYPE_LOOKUP_CODE IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV')
AND PA_ADDITION_FLAG in ('E', 'M', 'N')
AND POSTED_FLAG = 'Y'
) DIST,
PA_Projects_ALL PAP,
PJM_Org_Parameters POP,
PO_Distributions_All POD
WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
AND DIST.Invoice_Id = INV.Invoice_Id
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 DIST.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and DIST.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and DIST.Accounting_Date <= L_End_Date ))
AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
AND DIST.po_distribution_id = POD.po_distribution_id
AND POP.Organization_Id = POD.Destination_Organization_Id
AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
AND PAP.Project_Id = X_Project_ID
ORDER BY 9,1,2
for update;
SELECT INV.Invoice_id Invoice_Id
, DIST.Invoice_Distribution_Id Invoice_Distribution_Id
, PAP.Org_Id Proj_Org_Id
, 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
, POD.Org_Id Org_Id
, DIST.description Expenditure_Comment
, 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.Amount Charge_Amount
, DIST.Base_Amount Base_Charge_Amount
, DIST.PO_Distribution_Id PO_Distribution_Id
, DIST.RCV_Transaction_Id RCV_Transaction_Id
, DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
FROM AP_Invoices_All INV,
(SELECT C.Invoice_Distribution_Id
, C.Invoice_Id
, P.Project_Id
, P.Task_Id
, C.Accounting_Date
, C.Expenditure_Organization_Id
, C.description
, C.Pa_Quantity
, C.Dist_Code_Combination_Id
, C.Accts_Pay_Code_Combination_Id
, C.Amount
, C.Base_Amount
, NVL(P.PO_Distribution_Id,(SELECT PO_Distribution_Id FROM AP_Invoice_Distributions_all P1
WHERE P1.invoice_distribution_id = P.charge_applicable_to_dist_id)) PO_Distribution_Id -- bugfix 7482789
, C.RCV_Transaction_Id
, C.Line_Type_Lookup_Code
FROM AP_Invoice_Distributions_all C, AP_Invoice_Distributions_all P
WHERE C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS','NONREC_TAX') -- bugfix 7482789
AND C.PA_ADDITION_FLAG in ('E', 'M', 'N')
AND C.POSTED_FLAG = 'Y'
AND C.charge_applicable_to_dist_id = P.invoice_distribution_id
AND (P.charge_applicable_to_dist_id IS NOT NULL OR C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS')) -- bugfix 7482789
) DIST,
PA_Projects_ALL PAP,
PJM_Org_Parameters POP,
PO_Distributions_All POD
WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
AND DIST.Invoice_Id = INV.Invoice_Id
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 DIST.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and DIST.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and DIST.Accounting_Date <= L_End_Date ))
AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
AND DIST.po_distribution_id = POD.po_distribution_id
AND POP.Organization_Id = POD.Destination_Organization_Id
AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
AND PAP.Project_Id = X_Project_ID
ORDER BY 9,1,2
for update;
SELECT POD.Destination_Type_Code
, POL.Item_ID
, POD.Bom_Resource_ID Wip_Resource_Id
, POD.Destination_Organization_ID
FROM po_distributions_all pod
, po_lines_all pol
WHERE POD.PO_Distribution_ID = P_Distribution_ID
AND POL.Po_line_ID = POD.Po_Line_ID;
select ap.base_currency_code
into l_base_currency_code
from ap_system_parameters_all ap
where ap.org_id = InvRec.Org_Id;
select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
'ERV', l_ERV_Exp_Comment,
'FREIGHT', l_Freight_Exp_Comment,
'TIPV', l_Tax_Exp_Comment,
'TERV', l_ERV_Exp_Comment,
'TRV', l_Tax_Exp_Comment,
'MISCELLANEOUS', l_Misc_Exp_Comment, null)
into l_exp_comment
from dual;
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');
UPDATE AP_Invoice_Distributions_all
SET pa_addition_flag = 'G'
WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
from ap_invoice_distributions_all
where Invoice_Id = InvRec.Invoice_Id
and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
UPDATE AP_Invoice_Distributions_all
SET pa_addition_flag = 'G'
WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
from ap_invoice_distributions_all
where Invoice_Id = InvRec.Invoice_Id
and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
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;
select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
into l_week_ending_day_index
FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
into l_week_ending_day from dual;
select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
into l_week_ending
from dual;
INSERT INTO pa_transaction_interface_all
(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,
inventory_item_id,
unit_of_measure,
wip_resource_id,
org_id
)
SELECT
l_transaction_source
, l_Batch_Name
, l_week_ending --pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
, NULL
, ORG.Name
, InvRec.Expenditure_Item_Date
, InvRec.Project_Number
, TASK.Task_Number
, l_Exp_Type
, InvRec.PA_Quantity
, NVL( InvRec.Expenditure_Comment , l_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
, l_linkage
, l_trx_status_code
, l_base_currency_code /* denom_currency_code */
, l_denom_raw_cost /* denom_raw_cost */
, l_denom_burdened_cost /* denom_burdened_cost */
, InvRec.Exchange_Date /* acct_rate_date */
, l_User_Conv_Type /* acct_rate_type */
, InvRec.Exchange_Rate /* acct_exchange_rate */
, l_acct_raw_cost /* acct_raw_cost */
, l_acct_burdened_cost /* acct_burdened_cost */
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, PoRec.item_id
, l_uom
, PoRec.wip_resource_id
, InvRec.Org_Id
FROM
AP_Invoice_Distributions_all DIST
, PA_Tasks TASK
, HR_Organization_Units ORG
WHERE
DIST.Invoice_Id = InvRec.Invoice_Id
AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
AND TASK.Task_Id = l_Task_Id;
UPDATE AP_Invoice_Distributions_all
SET Pa_Addition_Flag = 'Y',
Request_Id = l_request_id
WHERE Invoice_Id = InvRec.Invoice_Id
AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
'ERV', l_ERV_Exp_Comment,
'FREIGHT', l_Freight_Exp_Comment,
'TIPV', l_Tax_Exp_Comment,
'TERV', l_ERV_Exp_Comment,
'TRV', l_Tax_Exp_Comment,
'MISCELLANEOUS', l_Misc_Exp_Comment,
'NONREC_TAX', l_Tax_Exp_Comment,null) --bugfix 7482789
into l_exp_comment
from dual;
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');
UPDATE AP_Invoice_Distributions_all
SET pa_addition_flag = 'G'
WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
from ap_invoice_distributions_all
where Invoice_Id = InvRec.Invoice_Id
and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
UPDATE AP_Invoice_Distributions_all
SET pa_addition_flag = 'G'
WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
from ap_invoice_distributions_all
where Invoice_Id = InvRec.Invoice_Id
and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
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;
select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
into l_week_ending_day_index
FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
into l_week_ending_day from dual;
select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
into l_week_ending
from dual;
INSERT INTO pa_transaction_interface_all
(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,
inventory_item_id,
unit_of_measure,
wip_resource_id,
org_id
)
SELECT
l_transaction_source
, l_Batch_Name
, l_week_ending --pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
, NULL
, ORG.Name
, InvRec.Expenditure_Item_Date
, InvRec.Project_Number
, TASK.Task_Number
, l_Exp_Type
, InvRec.PA_Quantity
, NVL( InvRec.Expenditure_Comment , l_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
, l_linkage
, l_trx_status_code
, l_base_currency_code /* denom_currency_code */
, l_denom_raw_cost /* denom_raw_cost */
, l_denom_burdened_cost /* denom_burdened_cost */
, InvRec.Exchange_Date /* acct_rate_date */
, l_User_Conv_Type /* acct_rate_type */
, InvRec.Exchange_Rate /* acct_exchange_rate */
, l_acct_raw_cost /* acct_raw_cost */
, l_acct_burdened_cost /* acct_burdened_cost */
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, PoRec.item_id
, l_uom
, PoRec.wip_resource_id
, InvRec.Org_Id
FROM
AP_Invoice_Distributions_all DIST
, PA_Tasks TASK
, HR_Organization_Units ORG
WHERE
DIST.Invoice_Id = InvRec.Invoice_Id
AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
AND TASK.Task_Id = l_Task_Id;
UPDATE AP_Invoice_Distributions_all
SET Pa_Addition_Flag = 'Y',
Request_Id = l_request_id
WHERE Invoice_Id = InvRec.Invoice_Id
AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
select ap.base_currency_code
into l_base_currency_code
from ap_system_parameters_all ap
where ap.org_id = InvRec.Org_Id;
select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
'ERV', l_ERV_Exp_Comment,
'FREIGHT', l_Freight_Exp_Comment,
'TAX', l_Tax_Exp_Comment,
'TIPV', l_Tax_Exp_Comment,
'TERV', l_Tax_Exp_Comment,
'TRV', l_Tax_Exp_Comment,
'MISCELLANEOUS', l_Misc_Exp_Comment, null)
into l_exp_comment
from dual;
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');
UPDATE AP_Invoice_Distributions_all
SET pa_addition_flag = 'G'
WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
from ap_invoice_distributions_all
where Invoice_Id = InvRec.Invoice_Id
and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
UPDATE AP_Invoice_Distributions_all
SET pa_addition_flag = 'G'
WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
from ap_invoice_distributions_all
where Invoice_Id = InvRec.Invoice_Id
and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
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;
select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
into l_week_ending_day_index
FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
into l_week_ending_day from dual;
select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
into l_week_ending
from dual;
INSERT INTO pa_transaction_interface_all
(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,
Inventory_Item_Id,
Unit_Of_Measure,
Wip_Resource_Id,
Org_Id
)
SELECT
l_transaction_source
, l_Batch_Name
, l_week_ending -- pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
, NULL
, ORG.Name
, InvRec.Expenditure_Item_Date
, InvRec.Project_Number
, TASK.Task_Number
, l_Exp_Type
, InvRec.PA_Quantity
, NVL( InvRec.Expenditure_Comment , l_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
, l_linkage
, l_trx_status_code
, l_base_currency_code /* denom_currency_code */
, l_denom_raw_cost /* denom_raw_cost */
, l_denom_burdened_cost /* denom_burdened_cost */
, InvRec.Exchange_Date /* acct_rate_date */
, l_User_Conv_Type /* acct_rate_type */
, InvRec.Exchange_Rate /* acct_exchange_rate */
, l_acct_raw_cost /* acct_raw_cost */
, l_acct_burdened_cost /* acct_burdened_cost */
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, PoRec.Item_Id
, l_uom
, PoRec.Wip_Resource_Id
, InvRec.Org_Id
FROM
AP_Invoice_Distributions_all DIST
, PA_Tasks TASK
, HR_Organization_Units ORG
WHERE
DIST.Invoice_Id = InvRec.Invoice_Id
AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
-- AND DIST.PA_Addition_Flag = 'S'
AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
AND TASK.Task_Id = l_Task_Id;
UPDATE AP_Invoice_Distributions_all
SET Pa_Addition_Flag = 'Y',
Request_Id = l_request_id
WHERE Invoice_Id = InvRec.Invoice_Id
AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
'ERV', l_ERV_Exp_Comment,
'FREIGHT', l_Freight_Exp_Comment,
'TAX', l_Tax_Exp_Comment,
'TIPV', l_Tax_Exp_Comment,
'TERV', l_Tax_Exp_Comment,
'TRV', l_Tax_Exp_Comment,
'MISCELLANEOUS', l_Misc_Exp_Comment,
'NONREC_TAX', l_Tax_Exp_Comment,null) --bugfix 7482789
into l_exp_comment
from dual;
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');
UPDATE AP_Invoice_Distributions_all
SET pa_addition_flag = 'G'
WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
from ap_invoice_distributions_all
where Invoice_Id = InvRec.Invoice_Id
and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
UPDATE AP_Invoice_Distributions_all
SET pa_addition_flag = 'G'
WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
from ap_invoice_distributions_all
where Invoice_Id = InvRec.Invoice_Id
and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
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;
select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
into l_week_ending_day_index
FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
into l_week_ending_day from dual;
select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
into l_week_ending
from dual;
INSERT INTO pa_transaction_interface_all
(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,
Inventory_Item_Id,
Unit_Of_Measure,
Wip_Resource_Id,
Org_Id
)
SELECT
l_transaction_source
, l_Batch_Name
, l_week_ending -- pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
, NULL
, ORG.Name
, InvRec.Expenditure_Item_Date
, InvRec.Project_Number
, TASK.Task_Number
, l_Exp_Type
, InvRec.PA_Quantity
, NVL( InvRec.Expenditure_Comment , l_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
, l_linkage
, l_trx_status_code
, l_base_currency_code /* denom_currency_code */
, l_denom_raw_cost /* denom_raw_cost */
, l_denom_burdened_cost /* denom_burdened_cost */
, InvRec.Exchange_Date /* acct_rate_date */
, l_User_Conv_Type /* acct_rate_type */
, InvRec.Exchange_Rate /* acct_exchange_rate */
, l_acct_raw_cost /* acct_raw_cost */
, l_acct_burdened_cost /* acct_burdened_cost */
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, PoRec.Item_Id
, l_uom
, PoRec.Wip_Resource_Id
, InvRec.Org_Id
FROM
AP_Invoice_Distributions_all DIST
, PA_Tasks TASK
, HR_Organization_Units ORG
WHERE
DIST.Invoice_Id = InvRec.Invoice_Id
AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
-- AND DIST.PA_Addition_Flag = 'S'
AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
AND TASK.Task_Id = l_Task_Id;
UPDATE AP_Invoice_Distributions_all
SET Pa_Addition_Flag = 'Y',
Request_Id = l_request_id
WHERE Invoice_Id = InvRec.Invoice_Id
AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;