[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hold_lookup_code from ap_holds_all
where invoice_id= p_invoice_id
and hold_lookup_code in ('Pay When Paid','PO Deliverable')
and release_reason IS NULL;
Update PA_PWP_AP_INV_HDR Set RELHOLD_REJ_REASON = SubStr(l_err_msg,1,2000)
Where invoice_id = p_inv_tbl(Inv_RelHOld_rec);
Update PA_PWP_AP_INV_HDR Set HOLD_REASON = '',
PWP_HOLD_FLAG = 'N',
DLV_HOLD_FLAG = 'N'
Where Invoice_Id = p_inv_tbl(Inv_RelHOld_rec)
And RELHOLD_REJ_REASON Is Null;
select apinv.invoice_id Invoice_Id,
apinv.invoice_num invoice_num,
vend.vendor_id vendor_Id,
vend.vendor_name Supplier_name,
vend.segment1 Supplier_Num,
apinv.Invoice_Date Invoice_Date,
P_project_id Project_Id,
apinv.invoice_Currency_code invoice_Currency,
apinv.payment_Currency_code Payment_Currency,
apinv.exchange_rate Exchange_Rate,
(select vendor_site_code from
po_vendor_sites_all
where vendor_id = apinv.vendor_id
and vendor_site_id = apinv.vendor_site_id) Supplier_Site,
Invoice_Amount,
decode(apinv.payment_Currency_code, apinv.invoice_Currency_code,
sum(amount_remaining),
sum(amount_remaining)/nvl(exchange_rate,1)) UnPaid_Inv_Amt,
(decode(apinv.payment_Currency_code,
apinv.invoice_Currency_code,
sum(gross_amount),
sum(gross_amount)/nvl(apinv.exchange_rate,1)) -
decode(apinv.payment_Currency_code,
apinv.invoice_Currency_code,
sum(amount_remaining),
sum(amount_remaining)/nvl(apinv.exchange_rate,1))) Paid_Inv_Amt
from ap_invoices_all apinv,
ap_payment_schedules_all appay,
po_vendors vend
where exIsts (select 1 from
ap_invoice_dIstributions_all apd
where apd.project_id = P_project_Id
and apd.posted_flag ='Y'
and apinv.invoice_id = apd.invoice_id)
and appay.invoice_id(+)=apinv.invoice_id
and vend.vendor_id = apinv.vendor_id
--and apinv.invoice_amount !=0 -- Bug# 7713608
and apinv.invoice_type_lookup_code <> 'EXPENSE REPORT'
and apinv.invoice_id = NVL(G_Invoice_Id,apinv.invoice_id)
group by apinv.invoice_id, apinv.invoice_num,
vend.vendor_name,apinv.invoice_Date, apinv.invoice_Currency_code,
apinv.vendor_id,apinv.vendor_site_id, apinv.Invoice_Amount, vend.vendor_id,
apinv.payment_Currency_code,apinv.exchange_rate,vend.segment1
order by apinv.invoice_id;
select nvl((decode(apinv.payment_Currency_code,
apinv.invoice_Currency_code,
sum(gross_amount),
sum(gross_amount)/nvl(apinv.exchange_rate,1)) -
decode(apinv.payment_Currency_code,
apinv.invoice_Currency_code,
sum(amount_remaining),
sum(amount_remaining)/nvl(apinv.exchange_rate,1))),0) Paid_Inv_Amt
from ap_invoices_all apinv,
ap_payment_schedules_all appay
where apinv.invoice_id = p_invoice_id and exIsts (select 1 from
ap_invoice_dIstributions_all apd
where apd.project_id = P_project_Id
and apd.posted_flag ='Y'
and apinv.invoice_id = apd.invoice_id)
and appay.invoice_id(+)=apinv.invoice_id
and apinv.invoice_type_lookup_code <> 'EXPENSE REPORT'
and exists (
(SELECT 1
FROM ap_invoice_payments_all invpay ,
pa_projects_all proj,
pa_implementations_all imp,
GL_PERIOD_STATUSES glp
WHERE proj.project_id = p_project_id
and invpay.invoice_id = appay.invoice_id
and invpay.payment_num = appay.payment_num
and glp.application_id = 101
AND glp.adjustment_period_flag = 'N'
AND glp.set_of_books_id = imp.set_of_books_id --Bug# 7713608
AND glp.closing_status = 'O'
and imp.org_id = proj.org_id
GROUP BY glp.application_id,glp.adjustment_period_flag,glp.closing_status
HAVING max(invpay.accounting_date)
BETWEEN MAX(glp.start_date) AND MAX(glp.end_date)
)) group by apinv.payment_Currency_code,
apinv.invoice_Currency_code, apinv.exchange_rate;
select apd.invoice_id,
apd.project_id,
apd.task_id,
apd.Expenditure_Item_Date,
pod.po_header_id,
sum(apd.amount) ProjInvAmount,
sum(ap_pay_hd.amount) Disc_Taken_On_Invoice /* Bug# 7833675 */
from
ap_invoice_dIstributions_all apd,
po_dIstributions_all pod,
ap_payment_hist_dists ap_pay_hd
where apd.project_Id = p_project_Id
and apd.invoice_id = p_invoice_id
and apd.posted_flag = 'Y'
and pod.po_dIstribution_id(+) = apd.po_distribution_id
and ap_pay_hd.invoice_distribution_id(+) = apd.invoice_distribution_id /* Bug# 7833675 */
and ap_pay_hd.pay_dist_lookup_code(+) = 'DISCOUNT' /* Bug# 7833675 */
and apd.line_type_lookup_code <> 'RETAINAGE'
group by apd.invoice_id, apd.project_id, apd.task_id, apd.Expenditure_Item_Date,
pod.po_header_id
order by apd.project_id, apd.task_id, apd.Expenditure_Item_Date, apd.invoice_id,pod.po_header_id;
select sum(apd.amount) ProjRtngAmount from
ap_invoice_dIstributions_all apd,
po_dIstributions_all pod
where apd.project_Id = p_project_Id
and apd.invoice_id = p_invoice_id
and apd.posted_flag = 'Y'
and pod.po_dIstribution_id(+) = apd.po_distribution_id
and pod.po_header_id(+)=p_po_header_id
and apd.line_type_lookup_code = 'RETAINAGE'
and apd.task_id = p_task_id
and apd.expenditure_item_date = p_expenditure_item_date
group by apd.invoice_id, apd.project_Id, apd.task_id, apd.po_dIstribution_id, apd.expenditure_item_date;
Select hold_lookup_code, hold_reason, 'PWP/DLV' HoldType From ap_holds_all
Where invoice_id= P_Invoice_Id
And hold_lookup_code In ('Pay When Paid','PO Deliverable')
and RELEASE_REASON is null;
Select distinct draft_invoice_num,link_type From (
Select draft_invoice_num, 'M' link_type From PA_PWP_LINKED_INVOICES PWP
Where PWP.AP_INVOICE_ID = p_invoice_id
And PWP.PROJECT_ID = p_project_id
UNION ALL
Select pdii.draft_invoice_num, 'A' From PA_DRAFT_INVOICE_ITEMS PDII ,
PA_CUST_REV_DIST_LINES CRDL ,
PA_EXPENDITURE_ITEMS EI
Where PDII.project_id = crdl.project_id
And pdii.draft_invoice_num = crdl.draft_invoice_num
And pdii.line_num = crdl.draft_invoice_item_line_num
And crdl.expenditure_item_id = ei.expenditure_item_id
And ei.system_linkage_function = 'VI'
And ei.document_header_id =p_invoice_id
And ei.transaction_source like 'AP%'
And ei.project_id =p_project_id);
Select Segment1 PO_NUMBER From po_headers_all
Where po_header_id in (Select distinct po_header_id
from pa_pwp_ap_inv_dtl where invoice_id = p_Invoice_id);
Select distinct Invoice_Id From (
Select AP_Invoice_Id Invoice_Id From PA_PWP_LINKED_INVOICES PWP
Where PWP.draft_invoice_num = P_Draft_Inv_Num
And PWP.PROJECT_ID = p_project_id
And ap_Invoice_Id is not null
UNION ALL
Select ei.document_header_id From PA_DRAFT_INVOICE_ITEMS PDII ,
PA_CUST_REV_DIST_LINES CRDL ,
PA_EXPENDITURE_ITEMS EI
Where PDII.project_id = crdl.project_id
And pdii.draft_invoice_num = P_Draft_Inv_Num
AND pdii.draft_invoice_num = crdl.draft_invoice_num
And pdii.line_num = crdl.draft_invoice_item_line_num
And crdl.expenditure_item_id = ei.expenditure_item_id
And ei.system_linkage_function = 'VI'
And ei.transaction_source like 'AP%'
And ei.project_id =p_project_id);
Delete from PA_PWP_AP_INV_HDR where project_id = P_Project_Id;
Delete from PA_PWP_AP_INV_DTL where project_id = P_Project_Id;
Delete from PA_PWP_AP_INV_HDR where project_id = P_Project_Id And Invoice_Id = G_Invoice_Id;
Delete from PA_PWP_AP_INV_DTL where project_id = P_Project_Id And Invoice_Id = G_Invoice_Id;
SELECT PA_PWP_AP_INV_HDR_S.nextval
INTO L_PA_PWP_AP_HDR_ID
FROM dual;
log_message('Before inserting record in PA_PWP_AP_INV_HDR'||
'[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
'[l_stage : '||l_stage||'] '||
'[L_PA_PWP_AP_HDR_ID : '||L_PA_PWP_AP_HDR_ID||'] ',
'Process_SuppInv_Dtls1');
Insert Into PA_PWP_AP_INV_HDR(PA_PWP_AP_HDR_ID
,PROJECT_ID
,INVOICE_ID
,INVOICE_NUM
,vendOR_ID
,SUPPLIER_NUM
,SUPPLIER_NAME
,SUPPLIER_SITE_CODE
,INVOICE_Date
,INVOICE_AMOUNT
,INVOICE_Currency
,HOLD_REASON
,PWP_HOLD_FLAG
,DLV_HOLD_FLAG
,PAYMENT_STATUS
,LINKED_DRAFT_INVOICE_NUM
,LINKED_DRFAT_INV_TYPE
) Values(
l_PA_PWP_AP_HDR_ID
,INVREC.PROJECT_ID
,INVREC.INVOICE_ID
,INVREC.INVOICE_NUM
,INVREC.vendOR_ID
,INVREC.Supplier_Num
,INVREC.SUPPLIER_NAME
,INVREC.SUPPLIER_SITE
,INVREC.INVOICE_Date
,INVREC.INVOICE_AMOUNT
,INVREC.INVOICE_Currency
,l_hold_reason
,l_inv_pwp_hold
,l_inv_dlv_hold
,l_inv_paid
,l_draft_inv_number
,l_draft_inv_link_type
);
log_message('Before opening the loop for inserting record in PA_PWP_AP_INV_DTL '||
'[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
'[l_stage : '||l_stage||'] ',
'Process_SuppInv_Dtls1');
log_message('Before inserting record in PA_PWP_AP_INV_DTL '||
'[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
'[l_stage : '||l_stage||'] ',
'Process_SuppInv_Dtls1');
Insert into PA_PWP_AP_INV_DTL(PA_PWP_AP_HDR_ID
,INVOICE_ID
,PROJECT_ID
,TASK_ID
,Expenditure_Item_Date
,PO_HEADER_ID
,INVOICE_Currency
,PROJINV_TOT_AMOUNT
,AMOUNT_PAID
,AMOUNT_UNPAID
,RETAINED_AMOUNT
,DISCOUNT_AMOUNT
,ProjFunc_Currency_CODE
,ProjFunc_INVOICE_AMOUNT
,ProjFunc_INV_PAID_AMOUNT
,ProjFunc_INV_UNPAID_AMOUNT
,ProjFunc_RETAINED_AMOUNT
,ProjFunc_DISCOUNT_AMOUNT
,Proj_Currency_CODE
,Proj_INVOICE_AMOUNT
,Proj_INV_PAID_AMOUNT
,Proj_INV_UNPAID_AMOUNT
,Proj_RETAINED_AMOUNT
,Proj_DISCOUNT_AMOUNT
,ACCT_Currency_CODE
,ACCT_INVOICE_AMOUNT
,ACCT_INV_PAID_AMOUNT
,ACCT_INV_UNPAID_AMOUNT
,ACCT_RETAINED_AMOUNT
,ACCT_DISCOUNT_AMOUNT
,PROJFUNC_CUR_PER_INV_PAID
,PROJ_CUR_PER_INV_PAID) VALUES (
l_PA_PWP_AP_HDR_ID
,INVREC.INVOICE_ID
,INVREC.PROJECT_ID
,INVDTL.TASK_ID
,INVDTL.Expenditure_Item_Date
,INVDTL.PO_HEADER_ID
,INVREC.INVOICE_Currency
,INVOICE_AMOUNT
,AMOUNT_PAID
,AMOUNT_UNPAID
,Retainage
,DISCOUNT_AMOUNT
,ProjFunc_Currency
,ProjFunc_INVOICE_AMT
,ProjFunc_AMT_PAID
,ProjFunc_AMT_UNPAID
,ProjFunc_retainage
,ProjFunc_DISCOUNT_AMT
,Proj_Currency
,Proj_INVOICE_AMT
,Proj_AMT_PAID
,Proj_AMT_UNPAID
,Proj_Retainage
,Proj_DISCOUNT_AMT
,PA_CURR_CODE
,ACCT_INVOICE_AMT
,ACCT_AMT_PAID
,ACCT_AMT_UNPAID
,Acct_retainage
,ACCT_DISCOUNT_AMT
,ProjFunc_Cur_Per_AMT_PAID
,Proj_Cur_Per_AMT_PAID);
Update PA_PWP_AP_INV_HDR
Set po_number = l_po_number
Where pa_pwp_ap_hdr_id = l_pa_pwp_ap_hdr_id;