[Home] [Help]
83: -- Bug 4080732 END
84:
85: /**
86: * Private Procedure: MassUpdate_Releases
87: * Modifies: Column price_override, retroactive_date in po_line_locations,
88: * Authorization_status, revision_num in po_headers and po_releases.
89: * Effects: Selects the agreements( blankets and contracts) as specified
90: * by the concurrent parameters and selects the execution docs
91: * refering these agreements for retroactive price updates.
89: * Effects: Selects the agreements( blankets and contracts) as specified
90: * by the concurrent parameters and selects the execution docs
91: * refering these agreements for retroactive price updates.
92: * Get the new price based on the release/Std PO shipment values.
93: * If they are different, then update po_line_locations with the
94: * new price. In either case, update retoractive_date in
95: * po_line_locations with the retroactive_date in po_lines so that
96: * this shipment will not be picked up again unless blanket line
97: * is retroactively changed. Once all the releases are done, update
91: * refering these agreements for retroactive price updates.
92: * Get the new price based on the release/Std PO shipment values.
93: * If they are different, then update po_line_locations with the
94: * new price. In either case, update retoractive_date in
95: * po_line_locations with the retroactive_date in po_lines so that
96: * this shipment will not be picked up again unless blanket line
97: * is retroactively changed. Once all the releases are done, update
98: * po_headers or po_releases with the new revision number and set
99: * authorization_status to "Requires Reapproval" and initiate
108: --Name: MassUpdate_Releases
109: --Pre-reqs:
110: -- None.
111: --Modifies:
112: -- Column price_override, retroactive_date in po_line_locations,
113: -- Authorization_status, revision_num in po_headers and po_releases.
114: --Locks:
115: -- None.
116: --Function:
121: -- are retroactively changed.
122: -- Selects the blanket lines that have been retroactively changed
123: -- and selects all the releases againt BA (or std PO against GA).
124: -- Get the new price based on the release/Std PO shipment values.
125: -- If they are different, then update po_line_locations with the
126: -- new price. In either case, update retoractive_date in
127: -- po_line_locations with the retroactive_date in po_lines so that
128: -- this shipment will not be picked up again unless blanket line
129: -- is retroactively changed. Once all the releases are done, update
123: -- and selects all the releases againt BA (or std PO against GA).
124: -- Get the new price based on the release/Std PO shipment values.
125: -- If they are different, then update po_line_locations with the
126: -- new price. In either case, update retoractive_date in
127: -- po_line_locations with the retroactive_date in po_lines so that
128: -- this shipment will not be picked up again unless blanket line
129: -- is retroactively changed. Once all the releases are done, update
130: -- po_headers or po_releases with the new revision number and set
131: -- authorization_status to "Requires Reapproval" and initiate
186: poll.price_override, nvl(poll.need_by_date,sysdate),
187: por.po_release_id,
188: por.authorization_status, por.revision_num,
189: pora.revision_num
190: from po_line_locations poll,
191: po_releases_all por, --
192: po_releases_archive pora
193: where nvl(por.frozen_flag, 'N') = 'N'
194: and nvl(por.authorization_status, 'INCOMPLETE') IN
239: poll.price_override, nvl(poll.need_by_date,sysdate),
240: poh.po_header_id,
241: poh.authorization_status, poh.revision_num,
242: poha.revision_num
243: from po_headers_all poh, po_lines_all pol, po_line_locations_all poll,
244: po_headers_archive_all poha,financials_system_params_all fsp
245: where pol.from_line_id = l_po_line_id
246: and poh.po_header_id = pol.po_header_id
247: and poh.org_id = fsp.org_id --
250: --to rule out unneccesary spo linelocations
251: and ( p_qp_license_on = 'Y' OR
252: ( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --
253: and poll.shipment_num = (select min(poll.shipment_num)
254: from po_line_locations_all polt
255: where polt.po_line_id=pol.po_line_id)
256: and nvl(poh.frozen_flag, 'N') = 'N'
257: and nvl(poh.authorization_status, 'INCOMPLETE') IN
258: ('APPROVED', 'INCOMPLETE', 'REJECTED',
266: and nvl(poh.cancel_flag,'N') <> 'Y'
267: and nvl(poh.consigned_consumption_flag,'N') ='N'
268: and pol.po_line_id = poll.po_line_id
269: and not exists (Select 'billed received shipments'
270: from po_line_locations_all poll1
271: where poll1.po_line_id = pol.po_line_id
272: and ((poll1.accrue_on_receipt_flag = 'Y' and
273: poll1.quantity_received <> 0)
274: or
299: poll.price_override, nvl(poll.need_by_date,sysdate),
300: por.po_release_id,
301: por.authorization_status, por.revision_num,
302: pora.revision_num
303: from po_line_locations poll,
304: po_releases_all por, --
305: po_releases_archive pora
306: where nvl(por.frozen_flag, 'N') = 'N'
307: and nvl(por.authorization_status, 'INCOMPLETE') IN
336: poll.price_override, nvl(poll.need_by_date,sysdate),
337: poh.po_header_id,
338: poh.authorization_status, poh.revision_num,
339: poha.revision_num
340: from po_headers_all poh, po_lines_all pol, po_line_locations_all poll,
341: po_headers_archive_all poha, po_document_types_all_b pdt,
342: financials_system_params_all fsp
343: where pol.from_line_id = l_po_line_id
344: and poh.po_header_id = pol.po_header_id
348: and pdt.document_type_code = 'PO' -- Bug 3573266
349: and pdt.document_subtype = 'STANDARD' -- Bug 3573266
350: and (nvl(pdt.archive_external_revision_code,'PRINT') = 'APPROVE' -- Bug 3573266
351: or (not exists (Select 'billed received shipments' -- Bug 3565522
352: from po_line_locations_all poll1
353: where poll1.po_line_id = pol.po_line_id
354: and ((poll1.accrue_on_receipt_flag = 'Y' and
355: poll1.quantity_received <> 0)
356: or
359: --to rule out unneccesary spo linelocations
360: and ( p_qp_license_on = 'Y' OR
361: ( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --
362: and poll.shipment_num = (select min(poll.shipment_num)
363: from po_line_locations_all polt
364: where polt.po_line_id=pol.po_line_id)
365: and nvl(poh.frozen_flag, 'N') = 'N'
366: and nvl(poh.authorization_status, 'INCOMPLETE') IN
367: ('APPROVED', 'INCOMPLETE', 'REJECTED',
404: poh.revision_num,
405: poha.revision_num
406: FROM po_headers_all poh,
407: po_lines_all pol,
408: po_line_locations_all poll,
409: po_headers_archive_all poha,
410: financials_system_params_all fsp
411: WHERE pol.Contract_id = l_po_header_id
412: AND pol.from_header_id IS NULL
416: AND poll.shipment_num =
417: (
418: SELECT
419: min(poll.shipment_num)
420: FROM po_line_locations_all polt
421: WHERE polt.po_line_id=pol.po_line_id
422: )
423: AND nvl(poh.frozen_flag, 'N') = 'N'
424: AND nvl(poh.authorization_status, 'INCOMPLETE') IN ('APPROVED', 'INCOMPLETE', 'REJECTED', 'REQUIRES REAPPROVAL')
429: AND not exists
430: (
431: SELECT
432: 'billed received shipments'
433: FROM po_line_locations_all poll1
434: WHERE poll1.po_line_id = pol.po_line_id
435: AND
436: (
437: (
468: poh.revision_num,
469: poha.revision_num
470: FROM po_headers_all poh,
471: po_lines_all pol,
472: po_line_locations_all poll,
473: po_headers_archive_all poha,
474: po_document_types_all_b pdt,
475: financials_system_params_all fsp
476: WHERE pol.Contract_id = l_po_header_id
489: not exists
490: (
491: SELECT
492: 'billed received shipments'
493: FROM po_line_locations_all poll1
494: WHERE poll1.po_line_id = pol.po_line_id
495: AND
496: (
497: (
506: AND poll.shipment_num =
507: (
508: SELECT
509: min(poll.shipment_num)
510: FROM po_line_locations_all polt
511: WHERE polt.po_line_id=pol.po_line_id
512: )
513: AND nvl(poh.frozen_flag, 'N') = 'N'
514: AND nvl(poh.authorization_status, 'INCOMPLETE') IN ('APPROVED', 'INCOMPLETE', 'REJECTED', 'REQUIRES REAPPROVAL')
782: 'and pol.category_id = mca.category_id (+) ' ||
783: ' and (((nvl(poh.global_agreement_flag,''N'') = ''N'') ';
784: l_sql_str7 := ' and exists ' ||
785: ' (select ''has releases'' from ' ||
786: ' po_line_locations poll where '||
787: ' poll.po_line_id = pol.po_line_id '||
788: ' and poll.po_release_id is not null)) ';
789: l_sql_str8 := ' OR ' ||
790: '((nvl(poh.global_agreement_flag,''N'') = ''Y'') '||
1116: -- Bulk Update
1117: l_module := g_log_head||l_api_name||'.'||
1118: '060'||'.';
1119: /* Bug 2718565.
1120: * We need to update po_line_locations_all with
1121: * time stamp since we process the Std POs
1122: * against GA in other operating units.
1123: */
1124: FORALL processed_index in
1122: * against GA in other operating units.
1123: */
1124: FORALL processed_index in
1125: 1..l_po_line_loc_table.COUNT
1126: UPDATE po_line_locations_all
1127: SET retroactive_date= l_retroactive_date_table(i),
1128: last_update_date = g_sysdate,
1129: last_updated_by = g_user_id
1130: WHERE line_location_id =
1360: */
1361: -- Bulk Update
1362: FORALL processed_index in
1363: 1..l_po_line_loc_table.COUNT
1364: UPDATE po_line_locations
1365: SET retroactive_date= l_retroactive_date_table(i),
1366: last_update_date = g_sysdate,
1367: last_updated_by = g_user_id
1368: WHERE line_location_id =
1372: -- Remove the retroactive date for all the rows that
1373: -- were excluded for the invalid adj account
1374: FORALL exclude_index in
1375: 1..g_exclude_row_id_table.COUNT
1376: UPDATE po_line_locations
1377: SET retroactive_date = null,
1378: last_update_date = g_sysdate,
1379: last_updated_by = g_user_id
1380: WHERE rowid = g_exclude_row_id_table(exclude_index);
1546: '088'||'.';
1547:
1548: FORALL processed_index in
1549: 1..l_po_line_loc_table.COUNT
1550: UPDATE po_line_locations_all
1551: SET retroactive_date= l_retroactive_date_table(i),
1552: last_update_date = g_sysdate,
1553: last_updated_by = g_user_id
1554: WHERE line_location_id =
1623:
1624: l_module := g_log_head||l_api_name||'.'||'110'||'.';
1625: if l_temp_row_id_table.COUNT <> 0 then
1626: FORALL price_update_index in 1..l_temp_row_id_table.COUNT
1627: UPDATE po_line_locations_all
1628: SET price_override =
1629: l_temp_new_price_table(price_update_index),
1630: calculate_tax_flag = 'Y',
1631: manual_price_change_flag = 'N', --
1668: WHERE rowid =
1669: l_temp_row_id_table(price_update_index);
1670:
1671: FORALL price_update_index in 1..l_temp_row_id_table.COUNT
1672: UPDATE po_line_locations_all poll
1673: SET poll.price_override =
1674: l_temp_new_price_table(price_update_index),
1675: poll.calculate_tax_flag = 'Y',
1676: poll.last_update_date = g_sysdate,
2008:
2009:
2010: /* Bug 2714259.
2011: * Update approved_flag to 'R', last_update_date and
2012: * last_updated_by columns in po_line_locations for which
2013: * the price has been updated .
2014: */
2015: l_module := g_log_head||l_api_name||'.'||'020'||'.';
2016: SELECT row_id
2021: and nvl(authorization_status,'INCOMPLETE') = 'APPROVED';
2022:
2023: l_module := g_log_head||l_api_name||'.'||'030'||'.';
2024: FORALL release_update_index in 1..l_row_id_table.COUNT
2025: UPDATE po_line_locations poll
2026: SET poll.approved_flag = 'R',
2027: poll.last_update_date = g_sysdate,
2028: poll.last_updated_by = g_user_id
2029: WHERE rowid = l_row_id_table(release_update_index);
2154: WHERE po_header_id = g_po_header_id_table(doc_update_index);
2155:
2156: /* Bug 2714259.
2157: * Update approved_flag to 'R', last_update_date and
2158: * last_updated_by columns in po_line_locations for which
2159: * the price has been updated .
2160: */
2161: l_module := g_log_head||l_api_name||'.'||'020'||'.';
2162: SELECT row_id
2167: and nvl(authorization_status,'INCOMPLETE') = 'APPROVED';
2168:
2169: l_module := g_log_head||l_api_name||'.'||'030'||'.';
2170: FORALL ship_update_index in 1..l_row_id_table.COUNT
2171: UPDATE po_line_locations_all poll
2172: SET poll.approved_flag = 'R',
2173: poll.last_update_date = g_sysdate,
2174: poll.last_updated_by = g_user_id
2175: WHERE poll.po_line_id =
2202: /**
2203: * Private Procedure: Process_Price_Change
2204: * Modifies: updates the global variables with the release_id, revision_num
2205: * from the archive table, authorization_status, row_id of the
2206: * po_line_locations to be updated and the new price if it is different
2207: * from the old price.
2208: * Effects: Get the new price for the release shipment attributes and if
2209: * different update the global variables.This is called from
2210: * massupdate_releases procedure.
2238: l_po_line_id PO_LINES_ALL.po_line_id%TYPE;
2239:
2240: --
2241: l_quantity PO_LINES.quantity%TYPE;
2242: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
2243: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
2244: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
2245: l_from_line_id PO_LINES.from_line_id%TYPE;
2246: l_org_id po_lines.org_id%TYPE;
2239:
2240: --
2241: l_quantity PO_LINES.quantity%TYPE;
2242: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
2243: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
2244: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
2245: l_from_line_id PO_LINES.from_line_id%TYPE;
2246: l_org_id po_lines.org_id%TYPE;
2247: l_contract_id po_lines.contract_id%TYPE;
2240: --
2241: l_quantity PO_LINES.quantity%TYPE;
2242: l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
2243: l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
2244: l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
2245: l_from_line_id PO_LINES.from_line_id%TYPE;
2246: l_org_id po_lines.org_id%TYPE;
2247: l_contract_id po_lines.contract_id%TYPE;
2248: l_order_header_id po_lines.po_header_id%TYPE;
2287: IF p_global_agreement_flag = 'Y' THEN
2288:
2289: select po_line_id
2290: into l_po_line_id
2291: from po_line_locations_all
2292: where line_location_id = p_po_line_location_id ;
2293:
2294: l_std_po_price_change := 'Y';
2295:
2301:
2302: l_std_po_price_change := 'Y';
2303: select po_line_id
2304: into l_po_line_id
2305: from po_line_locations_all
2306: where line_location_id = p_po_line_location_id ;
2307:
2308:
2309: END IF;
2431: l_vendor_site_id,
2432: l_uom,
2433: l_in_unit_price,
2434: l_currency_code
2435: FROM po_line_locations_all PLL, po_lines_all POL,
2436: po_headers_all POH
2437: WHERE PLL.line_location_id = p_po_line_location_id
2438: AND POL.po_line_id = PLL.po_line_id
2439: AND POH.po_header_id = POL.po_header_id;
3178: SELECT 'Y'
3179: INTO l_retro_change
3180: FROM dual
3181: WHERE EXISTS (SELECT 'retroactive pricing changes'
3182: FROM po_line_locations poll,
3183: po_line_locations_archive polla
3184: WHERE poll.po_header_id = p_document_id
3185: AND poll.po_header_id =polla.po_header_id
3186: AND poll.line_location_id = polla.line_location_id
3179: INTO l_retro_change
3180: FROM dual
3181: WHERE EXISTS (SELECT 'retroactive pricing changes'
3182: FROM po_line_locations poll,
3183: po_line_locations_archive polla
3184: WHERE poll.po_header_id = p_document_id
3185: AND poll.po_header_id =polla.po_header_id
3186: AND poll.line_location_id = polla.line_location_id
3187: AND polla.latest_external_flag = 'Y'
3193: SELECT 'Y'
3194: INTO l_retro_change
3195: FROM dual
3196: WHERE EXISTS (SELECT 'retroactive pricing changes'
3197: FROM po_line_locations poll,
3198: po_line_locations_archive polla
3199: WHERE poll.po_release_id = p_document_id
3200: AND poll.po_header_id =polla.po_header_id
3201: AND poll.line_location_id = polla.line_location_id
3194: INTO l_retro_change
3195: FROM dual
3196: WHERE EXISTS (SELECT 'retroactive pricing changes'
3197: FROM po_line_locations poll,
3198: po_line_locations_archive polla
3199: WHERE poll.po_release_id = p_document_id
3200: AND poll.po_header_id =polla.po_header_id
3201: AND poll.line_location_id = polla.line_location_id
3202: AND polla.latest_external_flag = 'Y'
3278: PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Reset retroactive_date for Release');
3279: END IF; /* IF g_debug_stmt */
3280:
3281: -- SQL What: Find out any retroactive pricing change for this Release
3282: -- SQL Why : For Release, column po_line_locations.retroactive_date will
3283: -- be updated if any retroactive pricing changes, Reset it
3284: -- to the corresponding blanket line retroactive_date after
3285: -- processing retroactive pricing.
3286: UPDATE po_line_locations_all pll
3282: -- SQL Why : For Release, column po_line_locations.retroactive_date will
3283: -- be updated if any retroactive pricing changes, Reset it
3284: -- to the corresponding blanket line retroactive_date after
3285: -- processing retroactive pricing.
3286: UPDATE po_line_locations_all pll
3287: SET retroactive_date = (SELECT pl.retroactive_date
3288: FROM po_lines_all pl
3289: WHERE pl.po_line_id = pll.po_line_id),
3290: last_update_date = SYSDATE,
3380: poll.line_location_id,
3381: poll.quantity_billed,
3382: poll.price_override new_price,
3383: polla.price_override old_price
3384: FROM po_line_locations poll,
3385: po_line_locations_archive polla
3386: WHERE poll.po_header_id = p_po_header_id
3387: AND poll.po_release_id IS NULL
3388: AND ((poll.accrue_on_receipt_flag = 'Y' AND
3381: poll.quantity_billed,
3382: poll.price_override new_price,
3383: polla.price_override old_price
3384: FROM po_line_locations poll,
3385: po_line_locations_archive polla
3386: WHERE poll.po_header_id = p_po_header_id
3387: AND poll.po_release_id IS NULL
3388: AND ((poll.accrue_on_receipt_flag = 'Y' AND
3389: (poll.quantity_received > 0 OR
3405: poll.line_location_id,
3406: poll.quantity_billed,
3407: poll.price_override new_price,
3408: polla.price_override old_price
3409: FROM po_line_locations poll,
3410: po_line_locations_archive polla
3411: WHERE poll.po_release_id = p_po_release_id
3412: AND ((poll.accrue_on_receipt_flag = 'Y' AND
3413: (poll.quantity_received > 0 OR
3406: poll.quantity_billed,
3407: poll.price_override new_price,
3408: polla.price_override old_price
3409: FROM po_line_locations poll,
3410: po_line_locations_archive polla
3411: WHERE poll.po_release_id = p_po_release_id
3412: AND ((poll.accrue_on_receipt_flag = 'Y' AND
3413: (poll.quantity_received > 0 OR
3414: poll.quantity_billed > 0)) OR
3467: FROM po_headers poh,
3468: po_lines_all pol, --
3469: -- Bug 3393219, Consumption transaction owning org
3470: -- financials_system_parameters fsp,
3471: po_line_locations_all poll, --
3473: po_distributions_all pod, --
3474: -- Bug 3314204, 3303148
3475: po_distributions_archive_all poda --
3468: po_lines_all pol, --
3469: -- Bug 3393219, Consumption transaction owning org
3470: -- financials_system_parameters fsp,
3471: po_line_locations_all poll, --
3472: po_line_locations_archive_all polla, --
3474: -- Bug 3314204, 3303148
3475: po_distributions_archive_all poda --
3476: WHERE pol.po_header_id = p_po_header_id
3535: po_headers_all poh, --
3536: po_lines pol,
3537: -- Bug 3393219, Consumption transaction owning org
3538: -- financials_system_parameters fsp,
3539: po_line_locations_all poll, --
3541: po_distributions_all pod, --
3542: -- Bug 3314204, 3303148
3543: po_distributions_archive poda
3536: po_lines pol,
3537: -- Bug 3393219, Consumption transaction owning org
3538: -- financials_system_parameters fsp,
3539: po_line_locations_all poll, --
3540: po_line_locations_archive_all polla, --
3542: -- Bug 3314204, 3303148
3543: po_distributions_archive poda
3544: WHERE por.po_release_id = p_po_release_id
4336: SELECT 'N'
4337: INTO l_retro_proj_allowed
4338: FROM DUAL
4339: WHERE EXISTS (SELECT 'has project information'
4340: FROM PO_LINE_LOCATIONS_ALL pll,
4341: PO_DISTRIBUTIONS_ALL pod
4342: WHERE pll.po_line_id = p_po_line_id
4343: AND pod.line_location_id = pll.line_location_id
4344: AND ((NVL(pll.quantity_received,0) > 0 AND
4357: SELECT 'N'
4358: INTO l_retro_proj_allowed
4359: FROM DUAL
4360: WHERE EXISTS (SELECT 'has project information'
4361: FROM PO_LINE_LOCATIONS_ALL pll,
4362: PO_DISTRIBUTIONS_ALL pod
4363: WHERE pll.line_location_id = p_po_line_loc_id
4364: AND pod.line_location_id = pll.line_location_id
4365: AND ((NVL(pll.quantity_received,0) > 0 AND
4422: IS
4423:
4424: l_account_valid varchar2(1) := 'Y';
4425: l_retroprice_adj_account_id number;
4426: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4427: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
4428: l_accrue_flag PO_LINE_LOCATIONS.accrue_on_receipt_flag%TYPE;
4429: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4430: -- Bug 3541961
4423:
4424: l_account_valid varchar2(1) := 'Y';
4425: l_retroprice_adj_account_id number;
4426: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4427: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
4428: l_accrue_flag PO_LINE_LOCATIONS.accrue_on_receipt_flag%TYPE;
4429: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4430: -- Bug 3541961
4431: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4424: l_account_valid varchar2(1) := 'Y';
4425: l_retroprice_adj_account_id number;
4426: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4427: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
4428: l_accrue_flag PO_LINE_LOCATIONS.accrue_on_receipt_flag%TYPE;
4429: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4430: -- Bug 3541961
4431: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4432: l_org_id PO_HEADERS.org_id%TYPE;
4425: l_retroprice_adj_account_id number;
4426: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4427: l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
4428: l_accrue_flag PO_LINE_LOCATIONS.accrue_on_receipt_flag%TYPE;
4429: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4430: -- Bug 3541961
4431: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4432: l_org_id PO_HEADERS.org_id%TYPE;
4433: l_trans_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
4429: l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4430: -- Bug 3541961
4431: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4432: l_org_id PO_HEADERS.org_id%TYPE;
4433: l_trans_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
4434: l_logical_inv_org_id number;
4435:
4436: l_module varchar2(100);
4437: l_api_name CONSTANT VARCHAR2(50) := 'Is_Adjustment_Account_Valid';
4444: NVL(poh.consigned_consumption_flag,'N'),
4445: pll.ship_to_organization_id,
4446: poh.org_id,
4447: pll.transaction_flow_header_id -- Bug 3880758
4448: from po_line_locations_all pll,
4449: -- Bug 3541961
4450: po_headers_all poh
4451: where pll.po_line_id = p_po_line_id
4452: -- Bug 3541961
4459: NVL(por.consigned_consumption_flag,'N'),
4460: pll.ship_to_organization_id,
4461: por.org_id,
4462: null -- Bug 3880758
4463: from po_line_locations_all pll,
4464: -- Bug 3541961
4465: po_releases_all por
4466: where pll.line_location_id = p_po_line_loc_id
4467: -- Bug 3541961
4674: IS
4675:
4676: l_is_inv_org_period_open VARCHAR2(1) := 'Y';
4677: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4678: l_trans_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
4679: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4680: l_logical_inv_org_id MTL_TRANSACTION_FLOW_LINES.from_organization_id%TYPE;
4681: l_inv_org_id_period_check HR_ALL_ORGANIZATION_UNITS.organization_id%TYPE;
4682:
4675:
4676: l_is_inv_org_period_open VARCHAR2(1) := 'Y';
4677: l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4678: l_trans_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
4679: l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4680: l_logical_inv_org_id MTL_TRANSACTION_FLOW_LINES.from_organization_id%TYPE;
4681: l_inv_org_id_period_check HR_ALL_ORGANIZATION_UNITS.organization_id%TYPE;
4682:
4683: l_module VARCHAR2(100);
4687: CURSOR STD_PO_SHIPMENTS_CSR IS
4688: SELECT NVL(poh.consigned_consumption_flag, 'N'),
4689: pll.ship_to_organization_id,
4690: pll.transaction_flow_header_id
4691: FROM po_line_locations_all pll,
4692: po_headers_all poh
4693: WHERE pll.po_line_id = p_po_line_id
4694: AND pll.po_header_id = poh.po_header_id;
4695:
4696: CURSOR REL_SHIPMENTS_CSR IS
4697: SELECT NVL(por.consigned_consumption_flag, 'N'),
4698: pll.ship_to_organization_id,
4699: NULL
4700: FROM po_line_locations_all pll,
4701: po_releases_all por
4702: WHERE pll.line_location_id = p_po_line_loc_id
4703: AND pll.po_release_id = por.po_release_id;
4704: