[Home] [Help]
102: i_receipt_no IN NUMBER,
103: i_invoice_no IN NUMBER) IS
104: (Select /*+ OPTIMIZER_FEATURES_ENABLE('9.0.1') */
105: distinct aida.rcv_transaction_id transaction_id
106: from ap_invoice_distributions_all aida
107: WHERE aida.rcv_transaction_id is not null
108: and i_invoice_no IS NULL
109: and i_receipt_no is NULL
110: and exists (select 1 from rcv_transactions rt,
145: /* Invoice Lines Project
146: Removing reference to ap_chrg_allocations_all
147: */
148: and EXISTS(
149: SELECT 1 FROM ap_invoice_distributions_all aida2
150: WHERE aida.invoice_distribution_id = nvl(aida2.charge_applicable_to_dist_id, aida.invoice_distribution_id)
151: AND (aida2.accounting_date between l_start_date and l_end_date)
152: AND aida2.posted_flag = 'Y'
153: AND aida2.line_type_lookup_code <> 'REC_TAX'
157: AND rae.rcv_transaction_id = rt.transaction_id
158: AND rae.event_type_id = 1 -- RECEIVE
159: AND rae.trx_flow_header_id is not NULL)
160: UNION
161: select distinct rcv_transaction_id from ap_invoice_distributions_all aida
162: where ((aida.accounting_date between l_start_date and l_end_date))
163: and aida.posted_flag = 'Y'
164: /* Invoice Lines Project, TAX is now REC_TAX and NONREC_TAX */
165: and aida.line_type_lookup_code <> 'REC_TAX'
227: )
228: )
229:
230: UNION
231: ( select distinct rcv_transaction_id from ap_invoice_distributions_all aida
232: where aida.invoice_id = i_invoice_no
233: and i_invoice_no IS NOT NULL
234: and aida.rcv_transaction_id IS NOT NULL
235: and aida.line_type_lookup_code = 'ITEM'
886:
887:
888: if (l_accounting_event_id = 0) then --added for dropshipment project
889: -------------------------------------------------------------
890: -- 2.3 Get all posted Invoice lines from AP_INVOICE_DISTRIBUTIONS_ALL
891: -- which are matched to the receipt
892: --------------------------------------------------------------
893:
894: l_stmt_num := 85;
901: l_stmt_num := 90;
902:
903: SELECT count(rcv_transaction_id)
904: INTO l_inv_count
905: FROM ap_invoice_distributions_all ad1
906: WHERE ad1.rcv_transaction_id = c_rec.transaction_id AND
907: ((l_res_flag =1) AND (ad1.accounting_date between i_start_date and i_end_date)) OR (l_res_flag = 2)
908: AND ad1.posted_flag = 'Y' AND
909: /* Invoice Lines Project TAX is now REC_TAX and NONREC_TAX */
938: 'QUANTITY', ad2.unit_price), 0 ) unit_price, -- Invoice Currency
939: --------------------------------------------------------------------------------
940: nvl(ad2.base_amount, nvl(ad2.amount, 0)) base_amount
941: FROM
942: ap_invoice_distributions_all ad2,
943: -- J Changes -----------------------------------------------------------
944: RCV_TRANSACTIONS RT,
945: PO_LINES_ALL POL,
946: PO_LINE_LOCATIONS_ALL POLL, -- Added for Complex work Procurement
1008: NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
1009: INTO l_corr_invoice_id,
1010: l_corr_inv,
1011: l_correction_amount
1012: FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
1013: AP_INVOICES_ALL AP_INV
1014: /* Invoice Lines Project
1015: No root_distribution_id or xinv_parent_reversal_id
1016: now it'll just be represented by corrected_invoice_dist_id
1022: BEGIN
1023: /* Bug3891984 changes starts here */
1024: SELECT NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
1025: INTO l_correction_tax_amount
1026: FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
1027: WHERE AIDA.INVOICE_ID = l_corr_invoice_id
1028: /* Invoice Lines Project non-recoverable tax now is just NONREC_TAX */
1029: AND AIDA.LINE_TYPE_LOOKUP_CODE = 'NONREC_TAX';
1030:
1083: BEGIN
1084:
1085: /* Invoice Lines Project no more ap_chrg_allocations_all table */
1086: Select count(*) into l_chrg_present
1087: from ap_invoice_distributions_all
1088: where invoice_distribution_id = c_inv.invoice_distribution_id
1089: and charge_applicable_to_dist_id is not null;
1090:
1091: EXCEPTION
2679:
2680:
2681: if (l_accounting_event_id = 0) then --added for dropshipment project
2682: -------------------------------------------------------------
2683: -- 2.3 Get all posted Invoice lines from AP_INVOICE_DISTRIBUTIONS_ALL
2684: -- which are matched to the receipt
2685: --------------------------------------------------------------
2686:
2687: l_stmt_num := 86;
2694: l_stmt_num := 91;
2695:
2696: SELECT count(rcv_transaction_id)
2697: INTO l_inv_count
2698: FROM ap_invoice_distributions_all ad1
2699: WHERE ad1.rcv_transaction_id = c_rec.transaction_id
2700: AND ad1.accounting_date <= l_end_date
2701: AND ad1.posted_flag = 'Y' AND
2702: /* Invoice Lines Project TAX is now REC_TAX and NONREC_TAX */
2731: 'QUANTITY', ad2.unit_price), 0 ) unit_price, -- Invoice Currency
2732: --------------------------------------------------------------------------------
2733: nvl(ad2.base_amount, nvl(ad2.amount, 0)) base_amount
2734: FROM
2735: ap_invoice_distributions_all ad2,
2736: -- J Changes -----------------------------------------------------------
2737: RCV_TRANSACTIONS RT,
2738: PO_LINES_ALL POL,
2739: PO_LINE_LOCATIONS_ALL POLL, -- Added for Complex work Procurement
2799: NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
2800: INTO l_corr_invoice_id,
2801: l_corr_inv,
2802: l_correction_amount
2803: FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
2804: AP_INVOICES_ALL AP_INV
2805: /* Invoice Lines Project
2806: No root_distribution_id or xinv_parent_reversal_id
2807: now it'll just be represented by corrected_invoice_dist_id
2813: BEGIN
2814: /* Bug3891984 changes starts here */
2815: SELECT NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
2816: INTO l_correction_tax_amount
2817: FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
2818: WHERE AIDA.INVOICE_ID = l_corr_invoice_id
2819: /* Invoice Lines Project non-recoverable tax now is just NONREC_TAX */
2820: AND AIDA.LINE_TYPE_LOOKUP_CODE = 'NONREC_TAX';
2821:
2874: BEGIN
2875:
2876: /* Invoice Lines Project no more ap_chrg_allocations_all table */
2877: Select count(*) into l_chrg_present
2878: from ap_invoice_distributions_all
2879: where invoice_distribution_id = c_inv.invoice_distribution_id
2880: and charge_applicable_to_dist_id is not null;
2881:
2882: EXCEPTION
3292: -- Check if any allocations (both parent and child should be posted)
3293: -------------------------------------------------------
3294: /* Invoice Lines Project
3295: No more ap_chrg_allocations_all table. Now need to get all information
3296: through ap_invoice_distributions_all. To determine if a distribution is a
3297: charge, just examine whether the charge_applicable_to_dist_id is not null
3298: */
3299: SELECT count(1)
3300: INTO l_chg_count
3297: charge, just examine whether the charge_applicable_to_dist_id is not null
3298: */
3299: SELECT count(1)
3300: INTO l_chg_count
3301: FROM ap_invoice_distributions_all aida
3302: WHERE aida.posted_flag = 'Y'
3303: AND (((i_res_flag = 1)
3304: AND (aida.accounting_date BETWEEN i_start_date AND i_end_Date))
3305: OR (i_res_flag = 2))
3305: OR (i_res_flag = 2))
3306: AND aida.line_type_lookup_code <> 'REC_TAX'
3307: AND EXISTS (
3308: SELECT 'X'
3309: FROM ap_invoice_distributions_all aida2
3310: WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
3311: AND aida2.posted_flag = 'Y'
3312: AND (((i_res_flag = 1)
3313: AND (aida2.accounting_date BETWEEN i_start_date AND i_end_Date))
3331: -- Insert into CRACD all allocations level by level
3332: -------------------------------------------------------------
3333: /* Invoice Lines Project
3334: No more ap_chrg_allocations_all table. Now need to get all information
3335: through ap_invoice_distributions_all. To determine if a distribution is a
3336: charge, just examine whether the charge_applicable_to_dist_id is not null
3337: */
3338:
3339: INSERT INTO
3388: i_prog_id,
3389: SYSDATE,
3390: i_login_id
3391: FROM
3392: ap_invoice_distributions_all aida
3393: WHERE aida.posted_flag = 'Y'
3394: AND (((i_res_flag = 1) AND (aida.accounting_date BETWEEN i_start_date AND i_end_Date))
3395: OR (i_res_flag = 2))
3396: AND aida.line_type_lookup_code <> 'REC_TAX'
3395: OR (i_res_flag = 2))
3396: AND aida.line_type_lookup_code <> 'REC_TAX'
3397: AND EXISTS (
3398: SELECT 'X'
3399: FROM ap_invoice_distributions_all aida2
3400: WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
3401: AND aida2.posted_flag = 'Y'
3402: AND (((i_res_flag = 1) AND (aida2.accounting_date BETWEEN i_start_date AND i_end_Date))
3403: OR (i_res_flag = 2))
3410:
3411: l_stmt_num := 30;
3412:
3413: ----------------------------------------------------------
3414: -- Get the parent amount from the AP_INVOICE_DISTRIBUTIONS_ALL table
3415: -- and also the LINE TYPE
3416: -- and update the CRACD rows just created
3417: ----------------------------------------------------------
3418:
3422: cracd3.parent_amount = (
3423: SELECT
3424: nvl(ad7.base_amount,nvl(ad7.amount,0))
3425: FROM
3426: ap_invoice_distributions_all ad7
3427: WHERE
3428: ad7.invoice_distribution_id = cracd3.parent_invoice_dist_id) ,
3429: cracd3.line_type = (
3430: SELECT
3429: cracd3.line_type = (
3430: SELECT
3431: ad8.line_type_lookup_code
3432: FROM
3433: ap_invoice_distributions_all ad8
3434: WHERE
3435: ad8.invoice_distribution_id = cracd3.invoice_distribution_id)
3436: WHERE
3437: cracd3.parent_invoice_dist_id IS NOT NULL AND
3518: -- Check if any allocations (both parent and child should be posted)
3519: -------------------------------------------------------
3520: /* Invoice Lines Project
3521: No more ap_chrg_allocations_all table. Now need to get all information
3522: through ap_invoice_distributions_all. To determine if a distribution is a
3523: charge, just examine whether the charge_applicable_to_dist_id is not null
3524: */
3525:
3526: SELECT
3527: count(1)
3528: INTO
3529: l_chg_count
3530: FROM
3531: ap_invoice_distributions_all aida
3532: WHERE aida.posted_flag = 'Y'
3533: AND aida.accounting_date <= l_end_date
3534: AND aida.line_type_lookup_code <> 'REC_TAX'
3535: AND EXISTS (
3533: AND aida.accounting_date <= l_end_date
3534: AND aida.line_type_lookup_code <> 'REC_TAX'
3535: AND EXISTS (
3536: SELECT 'X'
3537: FROM ap_invoice_distributions_all aida2
3538: WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
3539: AND aida2.posted_flag = 'Y'
3540: AND aida2.accounting_date <= l_end_date
3541: AND aida2.line_type_lookup_code <> 'REC_TAX'
3558: -- Insert into CRACD all allocations level by level
3559: -------------------------------------------------------------
3560: /* Invoice Lines Project
3561: No more ap_chrg_allocations_all table. Now need to get all information
3562: through ap_invoice_distributions_all. To determine if a distribution is a
3563: charge, just examine whether the charge_applicable_to_dist_id is not null
3564: */
3565:
3566: INSERT INTO
3615: i_prog_id,
3616: SYSDATE,
3617: i_login_id
3618: FROM
3619: ap_invoice_distributions_all aida
3620: WHERE aida.posted_flag = 'Y'
3621: AND aida.accounting_date <= l_end_date
3622: AND aida.line_type_lookup_code <> 'REC_TAX'
3623: AND EXISTS (
3621: AND aida.accounting_date <= l_end_date
3622: AND aida.line_type_lookup_code <> 'REC_TAX'
3623: AND EXISTS (
3624: SELECT 'X'
3625: FROM ap_invoice_distributions_all aida2
3626: WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
3627: AND aida2.posted_flag = 'Y'
3628: AND aida2.accounting_date <= l_end_date
3629: AND aida2.line_type_lookup_code <> 'REC_TAX'
3635:
3636: l_stmt_num := 30;
3637:
3638: ----------------------------------------------------------
3639: -- Get the parent amount from the AP_INVOICE_DISTRIBUTIONS_ALL table
3640: -- and also the LINE TYPE
3641: -- and update the CRACD rows just created
3642: ----------------------------------------------------------
3643:
3648: cracd3.parent_amount = (
3649: SELECT
3650: nvl(ad7.base_amount,nvl(ad7.amount,0))
3651: FROM
3652: ap_invoice_distributions_all ad7
3653: WHERE
3654: ad7.invoice_distribution_id = cracd3.parent_invoice_dist_id) ,
3655: cracd3.line_type = (
3656: SELECT
3655: cracd3.line_type = (
3656: SELECT
3657: ad8.line_type_lookup_code
3658: FROM
3659: ap_invoice_distributions_all ad8
3660: WHERE
3661: ad8.invoice_distribution_id = cracd3.invoice_distribution_id)
3662: WHERE
3663: cracd3.parent_invoice_dist_id IS NOT NULL AND