eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_DEDUCTIONS dependencies on PA_DEDUCTIONS

Line 1: PACKAGE BODY PA_DEDUCTIONS AS

1: PACKAGE BODY PA_DEDUCTIONS AS
2: -- /* $Header: PADEDTXB.pls 120.0.12020000.2 2012/07/19 09:33:01 admarath ship $
3:
4: TYPE g_dctn_hdr_amt IS RECORD (
5: p_dctn_hdr_id NUMBER,

Line 92: Procedure Update_Deduction_Status(p_dctn_hdr_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE,

88:
89: -- This procedure is to update the deduction request document status
90: -- in the whole cycle of creation of a deduction request to the creation
91: -- debit memo for that deduction request in payables.
92: Procedure Update_Deduction_Status(p_dctn_hdr_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE,
93: p_status IN VARCHAR2);
94:
95: /*---------------------------------------------------------------------------------------------------------
96: -- This procedure populates PA_DEDUCTIONS_ALL table after validating the data.

Line 96: -- This procedure populates PA_DEDUCTIONS_ALL table after validating the data.

92: Procedure Update_Deduction_Status(p_dctn_hdr_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE,
93: p_status IN VARCHAR2);
94:
95: /*---------------------------------------------------------------------------------------------------------
96: -- This procedure populates PA_DEDUCTIONS_ALL table after validating the data.
97: -- Input parameters
98: -- Parameters Type Required Description
99: -- p_dctn_hdr TABLE YES It stores the deduction header information
100: -- Out parameters

Line 130: select 'Y' from dual where not exists(select 1 from pa_deductions_all

126:
127: l_dctn_hdr g_dctn_hdrtbl;
128:
129: CURSOR C1(ded_id IN NUMBER) is
130: select 'Y' from dual where not exists(select 1 from pa_deductions_all
131: where deduction_req_id=ded_id);
132:
133: notexist VARCHAR2(1);
134:

Line 167: INSERT INTO PA_DEDUCTIONS_ALL(

163: IF P_DEBUG_MODE = 'Y' THEN
164: log_message ('Before inserting into header', g_api_name);
165: END IF;
166:
167: INSERT INTO PA_DEDUCTIONS_ALL(
168: deduction_req_id
169: ,project_id
170: ,vendor_id
171: ,vendor_site_id

Line 374: UPDATE PA_DEDUCTIONS_ALL SET total_amount = nvl((

370: /* Bug#8877035, used pl/sql table of column type instead of record type
371: to aviod the restrictions in 10g, 9i databases */
372:
373: FORALL I in 1..l_dctn_tbl_hdrid.COUNT --l_dctn_hdrtbl.COUNT
374: UPDATE PA_DEDUCTIONS_ALL SET total_amount = nvl((
375: SELECT SUM(amount) FROM
376: PA_DEDUCTION_TRANSACTIONS_ALL
377: WHERE deduction_req_id = l_dctn_tbl_hdrid(i) --l_dctn_hdrtbl(i).p_dctn_hdr_id
378: ),0) ,

Line 403: -- This procedure is to update existing data in PA_DEDUCTIONS_ALL table after validating the data.

399: p_return_status := 'U';
400: End;
401:
402: /*---------------------------------------------------------------------------------------------------------
403: -- This procedure is to update existing data in PA_DEDUCTIONS_ALL table after validating the data.
404: -- Input parameters
405: -- Parameters Type Required Description
406: -- p_dctn_hdr TABLE YES It stores the deduction header information
407: -- Out parameters

Line 451: UPDATE pa_deductions_all

447: g_api_name);
448: END IF;
449:
450: If Validate_Deduction_Hdr(l_Dctn_Hdr,P_msg_count, p_msg_data,p_return_status) Then
451: UPDATE pa_deductions_all
452: SET debit_memo_num = p_dctn_hdr(I).debit_memo_num
453: ,debit_memo_date = p_dctn_hdr(I).debit_memo_date
454: ,conversion_ratetype = p_dctn_hdr(I).conversion_ratetype
455: ,conversion_ratedate = p_dctn_hdr(I).conversion_ratedate

Line 585: UPDATE PA_DEDUCTIONS_ALL SET total_amount = nvl((

581: /* Bug#8877035, used pl/sql table of column type instead of record type
582: to aviod the restrictions in 10g, 9i databases */
583:
584: FORALL I in 1..l_dctn_tbl_hdrid.COUNT
585: UPDATE PA_DEDUCTIONS_ALL SET total_amount = nvl((
586: SELECT SUM(amount) FROM
587: PA_DEDUCTION_TRANSACTIONS_ALL
588: WHERE deduction_req_id = l_dctn_tbl_hdrid(i) --l_dctn_hdrtbl(i).p_dctn_hdr_id
589: ),0),

Line 607: -- This procedure is to delete existing data in PA_DEDUCTIONS_ALL table after validating the data.

603: p_return_status := 'U';
604: End;
605:
606: /*---------------------------------------------------------------------------------------------------------
607: -- This procedure is to delete existing data in PA_DEDUCTIONS_ALL table after validating the data.
608: -- Input parameters
609: -- Parameters Type Required Description
610: -- p_dctn_hdrid TABLE YES It stores the array of deducion requests
611: -- Out parameters

Line 644: DELETE PA_DEDUCTIONS_ALL WHERE deduction_req_id = p_dctn_hdrid(I) AND status NOT IN('PROCESSED','SUBMITTED');

640: log_message ('Before deleting the header information',g_api_name);
641: END IF;
642:
643: FORALL I IN 1..p_dctn_hdrid.COUNT
644: DELETE PA_DEDUCTIONS_ALL WHERE deduction_req_id = p_dctn_hdrid(I) AND status NOT IN('PROCESSED','SUBMITTED');
645:
646: IF P_DEBUG_MODE = 'Y' THEN
647: log_message ('Before deleting the transaction information',g_api_name);
648: END IF;

Line 652: SELECT 1 FROM PA_DEDUCTIONS_ALL WHERE deduction_req_id = p_dctn_hdrid(I) );

648: END IF;
649:
650: FORALL I IN 1..p_dctn_hdrid.COUNT
651: DELETE PA_DEDUCTION_TRANSACTIONS_ALL WHERE deduction_req_id = p_dctn_hdrid(I) AND NOT EXISTS(
652: SELECT 1 FROM PA_DEDUCTIONS_ALL WHERE deduction_req_id = p_dctn_hdrid(I) );
653: --Commit;
654: EXCEPTION
655: WHEN OTHERS THEN
656: IF P_DEBUG_MODE = 'Y' THEN

Line 700: UPDATE PA_DEDUCTIONS_ALL dctn_hdr SET total_amount = total_amount-nvl(

696: log_message ('Deducting the respective transaction amount from header',g_api_name);
697: END IF;
698:
699: FORALL I IN 1..p_dctn_txnid.COUNT
700: UPDATE PA_DEDUCTIONS_ALL dctn_hdr SET total_amount = total_amount-nvl(
701: (SELECT amount FROM PA_DEDUCTION_TRANSACTIONS_ALL dctn_txn
702: WHERE deduction_req_tran_id = p_dctn_txnid(I)
703: AND deduction_req_id = dctn_hdr.deduction_req_id
704: AND EXISTS (

Line 705: SELECT 1 FROM PA_DEDUCTIONS_ALL WHERE deduction_req_id = dctn_txn.deduction_req_id

701: (SELECT amount FROM PA_DEDUCTION_TRANSACTIONS_ALL dctn_txn
702: WHERE deduction_req_tran_id = p_dctn_txnid(I)
703: AND deduction_req_id = dctn_hdr.deduction_req_id
704: AND EXISTS (
705: SELECT 1 FROM PA_DEDUCTIONS_ALL WHERE deduction_req_id = dctn_txn.deduction_req_id
706: AND status NOT IN('PROCESSED','SUBMITTED','APPROVED'))),0);
707: IF P_DEBUG_MODE = 'Y' THEN
708: log_message ('Deleting the data from transaction table',g_api_name);
709: END IF;

Line 714: SELECT 1 FROM PA_DEDUCTIONS_ALL WHERE deduction_req_id = dctn_txn.deduction_req_id

710:
711: FORALL I IN 1..p_dctn_txnid.COUNT
712: DELETE PA_DEDUCTION_TRANSACTIONS_ALL dctn_txn WHERE deduction_req_tran_id = p_dctn_txnid(I)
713: AND EXISTS (
714: SELECT 1 FROM PA_DEDUCTIONS_ALL WHERE deduction_req_id = dctn_txn.deduction_req_id
715: AND status NOT IN('PROCESSED','SUBMITTED','APPROVED'));
716: --Commit;
717: EXCEPTION
718: WHEN OTHERS THEN

Line 757: CURSOR C1(p_dctn_req_num PA_DEDUCTIONS_ALL.deduction_req_num%TYPE,

753: ,p_return_status OUT NOCOPY VARCHAR2
754: ,p_calling_mode IN VARCHAR2 :=''
755: ) Return Boolean Is
756:
757: CURSOR C1(p_dctn_req_num PA_DEDUCTIONS_ALL.deduction_req_num%TYPE,
758: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) is
759: SELECT 'N'
760: FROM PA_DEDUCTIONS_ALL
761: WHERE deduction_req_num = p_dctn_req_num

Line 758: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) is

754: ,p_calling_mode IN VARCHAR2 :=''
755: ) Return Boolean Is
756:
757: CURSOR C1(p_dctn_req_num PA_DEDUCTIONS_ALL.deduction_req_num%TYPE,
758: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) is
759: SELECT 'N'
760: FROM PA_DEDUCTIONS_ALL
761: WHERE deduction_req_num = p_dctn_req_num
762: AND deduction_req_id <> p_dctn_req_id;

Line 760: FROM PA_DEDUCTIONS_ALL

756:
757: CURSOR C1(p_dctn_req_num PA_DEDUCTIONS_ALL.deduction_req_num%TYPE,
758: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) is
759: SELECT 'N'
760: FROM PA_DEDUCTIONS_ALL
761: WHERE deduction_req_num = p_dctn_req_num
762: AND deduction_req_id <> p_dctn_req_id;
763:
764: CURSOR C2(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,

Line 764: CURSOR C2(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,

760: FROM PA_DEDUCTIONS_ALL
761: WHERE deduction_req_num = p_dctn_req_num
762: AND deduction_req_id <> p_dctn_req_id;
763:
764: CURSOR C2(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
765: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
766: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
767: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) is
768: SELECT 'N'

Line 765: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,

761: WHERE deduction_req_num = p_dctn_req_num
762: AND deduction_req_id <> p_dctn_req_id;
763:
764: CURSOR C2(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
765: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
766: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
767: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) is
768: SELECT 'N'
769: FROM PA_DEDUCTIONS_ALL

Line 766: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,

762: AND deduction_req_id <> p_dctn_req_id;
763:
764: CURSOR C2(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
765: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
766: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
767: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) is
768: SELECT 'N'
769: FROM PA_DEDUCTIONS_ALL
770: WHERE debit_memo_num = p_debit_memo_num

Line 767: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) is

763:
764: CURSOR C2(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
765: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
766: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
767: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) is
768: SELECT 'N'
769: FROM PA_DEDUCTIONS_ALL
770: WHERE debit_memo_num = p_debit_memo_num
771: AND org_id = p_org_id

Line 769: FROM PA_DEDUCTIONS_ALL

765: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
766: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
767: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) is
768: SELECT 'N'
769: FROM PA_DEDUCTIONS_ALL
770: WHERE debit_memo_num = p_debit_memo_num
771: AND org_id = p_org_id
772: AND vendor_id = p_vendor_id
773: AND deduction_req_id <> nvl(p_dctn_req_id,-99);

Line 775: CURSOR C3(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,

771: AND org_id = p_org_id
772: AND vendor_id = p_vendor_id
773: AND deduction_req_id <> nvl(p_dctn_req_id,-99);
774:
775: CURSOR C3(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
776: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
777: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE) is
778: SELECT 'N'
779: FROM DUAL WHERE EXISTS (

Line 776: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,

772: AND vendor_id = p_vendor_id
773: AND deduction_req_id <> nvl(p_dctn_req_id,-99);
774:
775: CURSOR C3(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
776: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
777: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE) is
778: SELECT 'N'
779: FROM DUAL WHERE EXISTS (
780: SELECT 1

Line 777: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE) is

773: AND deduction_req_id <> nvl(p_dctn_req_id,-99);
774:
775: CURSOR C3(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
776: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
777: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE) is
778: SELECT 'N'
779: FROM DUAL WHERE EXISTS (
780: SELECT 1
781: FROM AP_INVOICES_ALL

Line 951: FROM PA_DEDUCTIONS_ALL

947: vendor_id,
948: po_number,
949: deduction_req_date,
950: org_id
951: FROM PA_DEDUCTIONS_ALL
952: WHERE deduction_req_id = p_dctn_req_id;
953:
954: CURSOR C2(c_exp_item_id NUMBER, p_dctn_txn_id NUMBER) IS
955: SELECT 'Y'

Line 965: l_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE;

961: SELECT PO_INQ_SV.get_po_total (type_lookup_code,
962: po_header_id,
963: '') FROM PO_HEADERS_ALL WHERE po_header_id = p_po_header_id;
964:
965: l_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE;
966: l_exp_item_exists VARCHAR2(1) := 'N';
967: tbl_dctn_hdr C1%ROWTYPE;
968:
969: CURSOR C4(p_etype VARCHAR2) IS

Line 1189: Procedure Submit_For_DebitMemo ( p_dctn_req_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE

1185: -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
1186: one error/warning message Otherwise the column is
1187: left blank.
1188: ----------------------------------------------------------------------------------------------------------*/
1189: Procedure Submit_For_DebitMemo ( p_dctn_req_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE
1190: ,p_msg_count OUT NOCOPY NUMBER
1191: ,p_msg_data OUT NOCOPY VARCHAR2
1192: ,p_return_status OUT NOCOPY VARCHAR2
1193: ) IS

Line 1204: p_next_number PA_DEDUCTIONS_ALL.debit_memo_num%TYPE;

1200: SELECT * FROM PA_DEDUCTION_TRANSACTIONS_ALL
1201: WHERE deduction_req_id = p_dctn_req_id;
1202:
1203: l_dctn_req_cnt NUMBER;
1204: p_next_number PA_DEDUCTIONS_ALL.debit_memo_num%TYPE;
1205:
1206: l_dctn_hdrtbl g_dctn_hdrtbl;
1207: l_dctn_txntbl g_dctn_txntbl;
1208: cnt NUMBER :=0;

Line 1327: UPDATE PA_DEDUCTIONS_ALL pda

1323: IF P_DEBUG_MODE = 'Y' THEN
1324: log_message ('Updating the debit memo number on Deduction request header', g_api_name);
1325: END IF;
1326:
1327: UPDATE PA_DEDUCTIONS_ALL pda
1328: SET debit_memo_num = l_dctn_hdrtbl(1).debit_memo_num
1329: WHERE deduction_req_id = p_dctn_req_id;
1330:
1331: COMMIT;

Line 1415: UPDATE PA_DEDUCTIONS_ALL pda

1411: IF P_DEBUG_MODE = 'Y' THEN
1412: log_message ('Header validation failed', g_api_name);
1413: END IF;
1414:
1415: UPDATE PA_DEDUCTIONS_ALL pda
1416: SET status = DECODE(p_return_status,'E','REJECTED',status)
1417: WHERE deduction_req_id = p_dctn_req_id;
1418:
1419: COMMIT;

Line 1463: CURSOR C2(p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) IS

1459: ,p_msg_data OUT NOCOPY VARCHAR2
1460: ,p_return_status OUT NOCOPY VARCHAR2
1461: ) IS
1462:
1463: CURSOR C2(p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) IS
1464: SELECT *
1465: FROM PA_DEDUCTION_TRANSACTIONS_ALL WHERE deduction_req_id = p_dctn_req_id;
1466:
1467: l_int_invoice_id NUMBER;

Line 1710: ,'PA_DEDUCTIONS_ALL'

1706: ,p_invoice_currency_code
1707: ,p_description
1708: ,p_invoice_num
1709: ,275
1710: ,'PA_DEDUCTIONS_ALL'
1711: ,p_deduction_req_id
1712: ,p_tax_flag
1713: ,l_groupid
1714: ,'Oracle Project Accounting'

Line 1796: Procedure Update_Deduction_Status(p_dctn_hdr_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE,

1792: g_api_name);
1793: END IF;
1794: End;
1795:
1796: Procedure Update_Deduction_Status(p_dctn_hdr_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE,
1797: p_status IN VARCHAR2) IS
1798: -- PRAGMA AUTONOMOUS_TRANSACTION;
1799: Begin
1800: UPDATE PA_DEDUCTIONS_ALL SET status = p_status WHERE deduction_req_id = p_dctn_hdr_id;

Line 1800: UPDATE PA_DEDUCTIONS_ALL SET status = p_status WHERE deduction_req_id = p_dctn_hdr_id;

1796: Procedure Update_Deduction_Status(p_dctn_hdr_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE,
1797: p_status IN VARCHAR2) IS
1798: -- PRAGMA AUTONOMOUS_TRANSACTION;
1799: Begin
1800: UPDATE PA_DEDUCTIONS_ALL SET status = p_status WHERE deduction_req_id = p_dctn_hdr_id;
1801: -- COMMIT;
1802: End;
1803: /* Bug 8740525 sosharma commented and added code for concurrent request */
1804: Procedure Import_DebitMemo(errbuf OUT NOCOPY varchar2,

Line 1830: WHERE product_table = 'PA_DEDUCTIONS_ALL'

1826: p_calling_sequence VARCHAR2(100);
1827:
1828: CURSOR C1 IS
1829: SELECT * FROM AP_INVOICES_INTERFACE
1830: WHERE product_table = 'PA_DEDUCTIONS_ALL'
1831: AND reference_key1 = p_dctn_req_id;
1832:
1833: CURSOR C2 IS
1834: SELECT * FROM PA_DEDUCTIONS_ALL

Line 1834: SELECT * FROM PA_DEDUCTIONS_ALL

1830: WHERE product_table = 'PA_DEDUCTIONS_ALL'
1831: AND reference_key1 = p_dctn_req_id;
1832:
1833: CURSOR C2 IS
1834: SELECT * FROM PA_DEDUCTIONS_ALL
1835: WHERE deduction_req_id = p_dctn_req_id;
1836:
1837:
1838: CURSOR C3 IS

Line 1844: from pa_deductions_all pded,pa_projects_all pa

1840: pded.debit_memo_date,pded.currency_code,pa.segment1,pa.name
1841: ,v.vendor_name
1842: ,vs.vendor_site_code
1843: ,hr.name hr_name
1844: from pa_deductions_all pded,pa_projects_all pa
1845: ,po_vendors v, po_vendor_sites_all vs,hr_organization_units hr
1846: where pa.project_id=pded.project_id and
1847: pded.vendor_id= v.vendor_id and pded.vendor_site_id=vs.vendor_site_id
1848: and pa.org_id=hr.organization_id

Line 1865: and ad.product_table = 'PA_DEDUCTIONS_ALL'

1861: fnd_lookup_values lookup
1862: where ar.parent_id=al.invoice_line_id
1863: and ar.parent_table='AP_INVOICE_LINES_INTERFACE'
1864: and al.invoice_id=ad.invoice_id
1865: and ad.product_table = 'PA_DEDUCTIONS_ALL'
1866: and al.task_id=pta.task_id
1867: and hr.organization_id=al.expenditure_organization_id
1868: and ar.reject_lookup_code = lookup.lookup_code
1869: and lookup.lookup_type='REJECT CODE'

Line 1882: and ad.product_table = 'PA_DEDUCTIONS_ALL'

1878: AP_INVOICES_INTERFACE ad,
1879: fnd_lookup_values lookup
1880: where ar.parent_id=ad.invoice_id
1881: and ar.parent_table='AP_INVOICES_INTERFACE'
1882: and ad.product_table = 'PA_DEDUCTIONS_ALL'
1883: and ar.reject_lookup_code = lookup.lookup_code
1884: and lookup.lookup_type='REJECT CODE'
1885: and view_application_id=200
1886: and lookup.language=USERENV('LANG')

Line 2165: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));

2161: NULL;
2162:
2163: DELETE AP_INVOICE_LINES_INTERFACE WHERE invoice_id IN(
2164: SELECT invoice_id FROM AP_INVOICES_INTERFACE WHERE
2165: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));
2166:
2167: DELETE AP_INVOICES_INTERFACE WHERE
2168: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id);
2169:

Line 2168: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id);

2164: SELECT invoice_id FROM AP_INVOICES_INTERFACE WHERE
2165: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));
2166:
2167: DELETE AP_INVOICES_INTERFACE WHERE
2168: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id);
2169:
2170: DELETE AP_INVOICE_DISTRIBUTIONS_ALL WHERE invoice_id IN(
2171: SELECT invoice_id FROM AP_INVOICES_ALL WHERE
2172: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));

Line 2172: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));

2168: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id);
2169:
2170: DELETE AP_INVOICE_DISTRIBUTIONS_ALL WHERE invoice_id IN(
2171: SELECT invoice_id FROM AP_INVOICES_ALL WHERE
2172: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));
2173:
2174: DELETE AP_INVOICE_LINES_ALL WHERE invoice_id IN(
2175: SELECT invoice_id FROM AP_INVOICES_ALL WHERE
2176: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));

Line 2176: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));

2172: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));
2173:
2174: DELETE AP_INVOICE_LINES_ALL WHERE invoice_id IN(
2175: SELECT invoice_id FROM AP_INVOICES_ALL WHERE
2176: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));
2177:
2178: DELETE AP_INVOICES_ALL WHERE
2179: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id);
2180:

Line 2179: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id);

2175: SELECT invoice_id FROM AP_INVOICES_ALL WHERE
2176: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id));
2177:
2178: DELETE AP_INVOICES_ALL WHERE
2179: product_table = 'PA_DEDUCTIONS_ALL' and reference_key1 = to_char(p_dctn_req_id);
2180:
2181: END Delete_Failed_Rec;
2182:
2183: Function Validate_DM( p_dctn_hdr IN OUT NOCOPY g_dctn_hdrtbl

Line 2189: CURSOR C1(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,

2185: ,p_msg_data OUT NOCOPY VARCHAR2
2186: ,p_return_status OUT NOCOPY VARCHAR2
2187: ) Return Boolean Is
2188:
2189: CURSOR C1(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
2190: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
2191: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
2192: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) IS
2193: SELECT 'N'

Line 2190: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,

2186: ,p_return_status OUT NOCOPY VARCHAR2
2187: ) Return Boolean Is
2188:
2189: CURSOR C1(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
2190: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
2191: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
2192: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) IS
2193: SELECT 'N'
2194: FROM PA_DEDUCTIONS_ALL

Line 2191: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,

2187: ) Return Boolean Is
2188:
2189: CURSOR C1(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
2190: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
2191: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
2192: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) IS
2193: SELECT 'N'
2194: FROM PA_DEDUCTIONS_ALL
2195: WHERE debit_memo_num = p_debit_memo_num

Line 2192: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) IS

2188:
2189: CURSOR C1(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
2190: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
2191: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
2192: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) IS
2193: SELECT 'N'
2194: FROM PA_DEDUCTIONS_ALL
2195: WHERE debit_memo_num = p_debit_memo_num
2196: AND org_id = p_org_id

Line 2194: FROM PA_DEDUCTIONS_ALL

2190: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
2191: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE,
2192: p_dctn_req_id PA_DEDUCTIONS_ALL.deduction_req_id%TYPE) IS
2193: SELECT 'N'
2194: FROM PA_DEDUCTIONS_ALL
2195: WHERE debit_memo_num = p_debit_memo_num
2196: AND org_id = p_org_id
2197: AND vendor_id = p_vendor_id
2198: AND deduction_req_id <> nvl(p_dctn_req_id,-99);

Line 2200: CURSOR C2(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,

2196: AND org_id = p_org_id
2197: AND vendor_id = p_vendor_id
2198: AND deduction_req_id <> nvl(p_dctn_req_id,-99);
2199:
2200: CURSOR C2(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
2201: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
2202: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE) IS
2203: SELECT 'N'
2204: FROM DUAL WHERE EXISTS (

Line 2201: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,

2197: AND vendor_id = p_vendor_id
2198: AND deduction_req_id <> nvl(p_dctn_req_id,-99);
2199:
2200: CURSOR C2(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
2201: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
2202: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE) IS
2203: SELECT 'N'
2204: FROM DUAL WHERE EXISTS (
2205: SELECT 1

Line 2202: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE) IS

2198: AND deduction_req_id <> nvl(p_dctn_req_id,-99);
2199:
2200: CURSOR C2(p_debit_memo_num PA_DEDUCTIONS_ALL.debit_memo_num%TYPE,
2201: p_org_id PA_DEDUCTIONS_ALL.org_id%TYPE,
2202: p_vendor_id PA_DEDUCTIONS_ALL.vendor_id%TYPE) IS
2203: SELECT 'N'
2204: FROM DUAL WHERE EXISTS (
2205: SELECT 1
2206: FROM AP_INVOICES_ALL

Line 2219: l_next_number PA_DEDUCTIONS_ALL.debit_memo_num%TYPE;

2215: AND org_id = p_org_id
2216: AND nvl(status, 'REJECTED') <> 'REJECTED');
2217:
2218: is_debit_memo_unique VARCHAR2(1) := 'Y';
2219: l_next_number PA_DEDUCTIONS_ALL.debit_memo_num%TYPE;
2220:
2221: l_msg_count NUMBER;
2222: l_msg_data VARCHAR2(4000);
2223: l_return_status VARCHAR2(1);

Line 2280: SELECT PA_DEDUCTIONS_DM_S.nextval

2276: log_message ('Generating debit memo number using sequence', g_api_name);
2277: END IF;
2278:
2279: LOOP
2280: SELECT PA_DEDUCTIONS_DM_S.nextval
2281: INTO l_next_number FROM sys.DUAL;
2282:
2283: OPEN C1(l_next_number,
2284: p_dctn_hdr(1).org_id,

Line 2396: AND product_table='PA_DEDUCTIONS_ALL'

2392: WHERE invoice_id = apinv.invoice_id
2393: AND parent_invoice_id = ( SELECT invoice_id from ap_invoices_all
2394: WHERE source = 'Oracle Project Accounting'
2395: AND invoice_type_lookup_code = 'DEBIT'
2396: AND product_table='PA_DEDUCTIONS_ALL'
2397: AND reference_key1 = ded_req_num));
2398:
2399: rval VARCHAR2(300) := NULL;
2400: BEGIN

Line 2478: END PA_DEDUCTIONS;

2474:
2475: */
2476:
2477:
2478: END PA_DEDUCTIONS;