[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PO_WF_DEBUG_PKG.insert_debug(g_wf_item_type,g_wf_item_key,text);
SELECT po_header_id,
decode(type_lookup_code,
'STANDARD', 'PO',
'BLANKET', 'PA',
'CONTRACT', 'PA', null),
type_lookup_code,
created_by
INTO
l_document_id,
l_document_type,
l_document_subtype,
l_preparer_id
FROM po_headers_merge_v
WHERE draft_id = p_draft_id;
select
/*1*/ pld.ACCRUE_ON_RECEIPT_FLAG
/*2*/ ,pld.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
/*3*/ ,pld.AMOUNT
/*4*/ ,pld.AMOUNT_ACCEPTED
/*5*/ ,pll.AMOUNT_BILLED -- to update draft
/*6*/ ,pld.AMOUNT_CANCELLED
/*7*/ ,pld.AMOUNT_FINANCED
/*8*/ ,pll.AMOUNT_RECEIVED -- to update draft
/*9*/ ,pld.AMOUNT_RECOUPED
/*10*/ ,pld.AMOUNT_REJECTED
/*1*/ --AMOUNT_SHIPPED
/*11*/ ,pld.APPROVED_DATE
/*12*/ ,pld.APPROVED_FLAG
/*13*/ ,pld.ATTRIBUTE_CATEGORY
/*14*/ ,pld.ATTRIBUTE1
/*15*/ ,pld.ATTRIBUTE10
/*16*/ ,pld.ATTRIBUTE11
/*17*/ ,pld.ATTRIBUTE12
/*18*/ ,pld.ATTRIBUTE13
/*19*/ ,pld.ATTRIBUTE14
/*20*/ ,pld.ATTRIBUTE15
/*21*/ ,pld.ATTRIBUTE2
/*22*/ ,pld.ATTRIBUTE3
/*23*/ ,pld.ATTRIBUTE4
/*24*/ ,pld.ATTRIBUTE5
/*25*/ ,pld.ATTRIBUTE6
/*26*/ ,pld.ATTRIBUTE7
/*27*/ ,pld.ATTRIBUTE8
/*28*/ ,pld.ATTRIBUTE9
/*29*/ ,pld.BID_PAYMENT_ID
/*30*/ ,pld.CALCULATE_TAX_FLAG
/*31*/ ,pld.CANCEL_DATE
/*32*/ ,pld.CANCEL_FLAG
/*33*/ ,pld.CANCEL_REASON
/*34*/ ,pld.CANCELLED_BY
/*35*/ ,pld.CHANGE_ACCEPTED_FLAG
/*36*/ ,pld.CHANGE_PROMISED_DATE_REASON
/*1*/ --CHANGE_STATUS
/*37*/ ,pld.CLOSED_BY
/*38*/ ,pld.CLOSED_CODE
/*39*/ ,pld.CLOSED_DATE
/*40*/ ,pld.CLOSED_FLAG
/*41*/ ,pld.CLOSED_FOR_INVOICE_DATE
/*42*/ ,pld.CLOSED_FOR_RECEIVING_DATE
/*43*/ ,pld.CLOSED_REASON
/*44*/ ,pld.CONSIGNED_FLAG
/*1*/ --CONTROL_ACTION
/*45*/ ,pld.COUNTRY_OF_ORIGIN_CODE
/*46*/ ,pld.CREATED_BY
/*47*/ ,pld.CREATION_DATE
/*48*/ ,pld.DAYS_EARLY_RECEIPT_ALLOWED
/*49*/ ,pld.DAYS_LATE_RECEIPT_ALLOWED
/*50*/ ,pld.DELETE_FLAG
/*51*/ ,pld.DESCRIPTION
/*52*/ ,pld.DRAFT_ID
/*53*/ ,pld.DROP_SHIP_FLAG
/*54*/ ,pld.ENCUMBER_NOW
/*55*/ ,pld.ENCUMBERED_DATE
/*56*/ ,pld.ENCUMBERED_FLAG
/*57*/ ,pld.END_DATE
/*58*/ ,pld.ENFORCE_SHIP_TO_LOCATION_CODE
/*59*/ ,pld.ESTIMATED_TAX_AMOUNT
/*60*/ ,pld.FINAL_MATCH_FLAG
/*61*/ ,pld.FIRM_DATE
/*62*/ , null
/*63*/ ,pld.FIRM_STATUS_LOOKUP_CODE
/*64*/ ,pld.FOB_LOOKUP_CODE
/*65*/ , null
/*66*/ ,pld.FREIGHT_TERMS_LOOKUP_CODE
/*67*/ , null
/*68*/ ,pld.FROM_HEADER_ID
/*69*/ ,pld.FROM_LINE_ID
/*70*/ ,pld.FROM_LINE_LOCATION_ID
/*71*/ ,pld.GLOBAL_ATTRIBUTE_CATEGORY
/*72*/ ,pld.GLOBAL_ATTRIBUTE1
/*73*/ ,pld.GLOBAL_ATTRIBUTE10
/*74*/ ,pld.GLOBAL_ATTRIBUTE11
/*75*/ ,pld.GLOBAL_ATTRIBUTE12
/*76*/ ,pld.GLOBAL_ATTRIBUTE13
/*77*/ ,pld.GLOBAL_ATTRIBUTE14
/*78*/ ,pld.GLOBAL_ATTRIBUTE15
/*79*/ ,pld.GLOBAL_ATTRIBUTE16
/*80*/ ,pld.GLOBAL_ATTRIBUTE17
/*81*/ ,pld.GLOBAL_ATTRIBUTE18
/*82*/ ,pld.GLOBAL_ATTRIBUTE19
/*83*/ ,pld.GLOBAL_ATTRIBUTE2
/*84*/ ,pld.GLOBAL_ATTRIBUTE20
/*85*/ ,pld.GLOBAL_ATTRIBUTE3
/*86*/ ,pld.GLOBAL_ATTRIBUTE4
/*87*/ ,pld.GLOBAL_ATTRIBUTE5
/*88*/ ,pld.GLOBAL_ATTRIBUTE6
/*89*/ ,pld.GLOBAL_ATTRIBUTE7
/*90*/ ,pld.GLOBAL_ATTRIBUTE8
/*91*/ ,pld.GLOBAL_ATTRIBUTE9
/*92*/ ,pld.GOVERNMENT_CONTEXT
/*93*/ , null
/*94*/ , null
/*95*/ , null
/*96*/ ,pld.INSPECTION_REQUIRED_FLAG
/*97*/ , null
/*98*/ ,pld.INVOICE_CLOSE_TOLERANCE
/*99*/ , null
/*100*/ , null
/*101*/ , null
/*102*/ ,pld.LAST_ACCEPT_DATE
/*103*/ ,pld.LAST_UPDATE_DATE
/*104*/ ,pld.LAST_UPDATE_LOGIN
/*105*/ ,pld.LAST_UPDATED_BY
/*106*/ ,pld.LEAD_TIME
/*107*/ ,pld.LEAD_TIME_UNIT
/*108*/ , null
/*109*/ , null
/*110*/ ,pld.LINE_LOCATION_ID
/*111*/ , null
/*112*/ , null
/*113*/ ,pld.MANUAL_PRICE_CHANGE_FLAG
/*114*/ ,pld.MATCH_OPTION
/*115*/ ,pld.MATCHING_BASIS
/*116*/ ,pld.NEED_BY_DATE
/*117*/ ,pld.NOTE_TO_RECEIVER
/*118*/ , null
/*119*/ ,pld.ORG_ID
/*1*/ --OUTSOURCED_ASSEMBLY
/*1*/ --OWNER_ROLE
/*1*/ --OWNER_USER_ID
/*120*/ ,pld.PAYMENT_TYPE
/*121*/ ,pld.PO_HEADER_ID
/*122*/ ,pld.PO_LINE_ID
/*123*/ ,pld.PO_RELEASE_ID
/*124*/ ,pld.PREFERRED_GRADE
/*125*/ ,pld.PRICE_DISCOUNT
/*126*/ ,pld.PRICE_OVERRIDE
/*127*/ ,pld.PROGRAM_APPLICATION_ID
/*128*/ ,pld.PROGRAM_ID
/*129*/ ,pld.PROGRAM_UPDATE_DATE
/*130*/ ,pld.PROMISED_DATE
/*131*/ , null
/*132*/ ,pld.QTY_RCV_EXCEPTION_CODE
/*133*/ ,pld.QTY_RCV_TOLERANCE
/*134*/ ,pld.QUANTITY
/*135*/ ,pld.QUANTITY_ACCEPTED
/*136*/ ,pll.QUANTITY_BILLED -- to update draft
/*137*/ ,pld.QUANTITY_CANCELLED
/*138*/ ,pld.QUANTITY_FINANCED
/*139*/ ,pll.QUANTITY_RECEIVED -- to update draft
/*140*/ ,pld.QUANTITY_RECOUPED
/*141*/ ,pld.QUANTITY_REJECTED
/*142*/ ,pld.QUANTITY_SHIPPED
/*143*/ ,pld.RECEIPT_DAYS_EXCEPTION_CODE
/*144*/ ,pld.RECEIPT_REQUIRED_FLAG
/*145*/ ,pld.RECEIVE_CLOSE_TOLERANCE
/*146*/ ,pld.RECEIVING_ROUTING_ID
/*147*/ ,pld.REQUEST_ID
/*148*/ ,pld.RETAINAGE_RELEASED_AMOUNT
/*149*/ ,pld.RETAINAGE_WITHHELD_AMOUNT
/*150*/ ,pld.RETROACTIVE_DATE
/*151*/ ,pld.SALES_ORDER_UPDATE_DATE
/*152*/ ,pld.SECONDARY_QUANTITY
/*153*/ ,pld.SECONDARY_QUANTITY_ACCEPTED
/*154*/ ,pld.SECONDARY_QUANTITY_CANCELLED
/*155*/ ,pld.SECONDARY_QUANTITY_RECEIVED
/*156*/ ,pld.SECONDARY_QUANTITY_REJECTED
/*157*/ ,pld.SECONDARY_QUANTITY_SHIPPED
/*158*/ ,pld.SECONDARY_UNIT_OF_MEASURE
/*159*/ ,pld.SHIP_TO_LOCATION_ID
/*160*/ ,pld.SHIP_TO_ORGANIZATION_ID
/*161*/ ,pld.SHIP_VIA_LOOKUP_CODE
/*162*/ ,pld.SHIPMENT_CLOSED_DATE
/*163*/ ,pld.SHIPMENT_NUM
/*164*/ ,pld.SHIPMENT_TYPE
/*165*/ ,pld.SOURCE_SHIPMENT_ID
/*166*/ ,pld.START_DATE
/*1*/ --STATUS
/*167*/ ,pld.SUPPLIER_ORDER_LINE_NUMBER
/*1*/ --TAX_ATTRIBUTE_UPDATE_CODE
/*168*/ ,pld.TAX_CODE_ID
/*169*/ ,pld.TAX_NAME
/*170*/ ,pld.TAX_USER_OVERRIDE_FLAG
/*171*/ ,pld.TAXABLE_FLAG
/*172*/ ,pld.TERMS_ID
/*173*/ , null
/*174*/ ,pld.TRANSACTION_FLOW_HEADER_ID
/*175*/ ,pld.UNENCUMBERED_QUANTITY
/*176*/ ,pld.UNIT_MEAS_LOOKUP_CODE
/*177*/ ,pld.UNIT_OF_MEASURE_CLASS
/*178*/ ,pld.USSGL_TRANSACTION_CODE
/*179*/ ,pld.VALUE_BASIS
/*180*/ ,pld.VMI_FLAG
/*181*/ ,pld.WORK_APPROVER_ID
/*pll.line_location_id,
pll.quantity_received,
pll.quantity_billed,
pll.amount_received,
pll.amount_billed*/
from po_line_locations_all pll, po_line_locations_draft_all pld
where pld.line_location_id = pll.line_location_id
and pld.draft_id = p_draft_id
and (pld.quantity_received <> pll.quantity_received or
pld.quantity_billed <> pll.quantity_billed or
pld.amount_received <> pll.amount_received or
pld.amount_billed <> pll.amount_billed);
select
/*1*/ pdd.ACCRUAL_ACCOUNT_ID
/*2*/ ,pdd.ACCRUE_ON_RECEIPT_FLAG
/*3*/ ,pdd.ACCRUED_FLAG
/*4*/ ,pd.AMOUNT_BILLED --updated to draft as part of sync
/*5*/ ,pdd.AMOUNT_CANCELLED
/*6*/ ,pdd.AMOUNT_DELIVERED
/*7*/ ,pdd.AMOUNT_FINANCED
/*8*/ ,pdd.AMOUNT_ORDERED
/*9*/ ,pdd.AMOUNT_RECOUPED
/*10*/ ,pdd.AMOUNT_TO_ENCUMBER
/*11*/ ,pdd.ATTRIBUTE_CATEGORY
/*12*/ ,pdd.ATTRIBUTE1
/*13*/ ,pdd.ATTRIBUTE10
/*14*/ ,pdd.ATTRIBUTE11
/*15*/ ,pdd.ATTRIBUTE12
/*16*/ ,pdd.ATTRIBUTE13
/*17*/ ,pdd.ATTRIBUTE14
/*18*/ ,pdd.ATTRIBUTE15
/*19*/ ,pdd.ATTRIBUTE2
/*20*/ ,pdd.ATTRIBUTE3
/*21*/ ,pdd.ATTRIBUTE4
/*22*/ ,pdd.ATTRIBUTE5
/*23*/ ,pdd.ATTRIBUTE6
/*24*/ ,pdd.ATTRIBUTE7
/*25*/ ,pdd.ATTRIBUTE8
/*26*/ ,pdd.ATTRIBUTE9
/*27*/ ,pdd.AWARD_ID
/*28*/ , null
/*29*/ ,pdd.BOM_RESOURCE_ID
/*30*/ ,pdd.BUDGET_ACCOUNT_ID
/*31*/ ,pdd.CHANGE_ACCEPTED_FLAG
/*1*/ --CHANGE_STATUS
/*32*/ ,pdd.CODE_COMBINATION_ID
/*33*/ ,pdd.CREATED_BY
/*34*/ ,pdd.CREATION_DATE
/*35*/ ,pdd.DELETE_FLAG
/*36*/ ,pdd.DELIVER_TO_LOCATION_ID
/*37*/ ,pdd.DELIVER_TO_PERSON_ID
/*38*/ ,pdd.DEST_CHARGE_ACCOUNT_ID
/*39*/ ,pdd.DEST_VARIANCE_ACCOUNT_ID
/*40*/ ,pdd.DESTINATION_CONTEXT
/*41*/ ,pdd.DESTINATION_ORGANIZATION_ID
/*42*/ ,pdd.DESTINATION_SUBINVENTORY
/*43*/ ,pdd.DESTINATION_TYPE_CODE
/*44*/ ,pdd.DISTRIBUTION_NUM
/*45*/ ,pdd.DISTRIBUTION_TYPE
/*46*/ ,pdd.DRAFT_ID
/*47*/ ,pdd.ENCUMBERED_AMOUNT
/*48*/ ,pdd.ENCUMBERED_FLAG
/*49*/ ,pdd.END_ITEM_UNIT_NUMBER
/*50*/ ,pdd.EXPENDITURE_ITEM_DATE
/*51*/ ,pdd.EXPENDITURE_ORGANIZATION_ID
/*52*/ ,pdd.EXPENDITURE_TYPE
/*53*/ , null
/*54*/ ,pdd.FAILED_FUNDS_LOOKUP_CODE
/*55*/ ,pdd.GL_CANCELLED_DATE
/*56*/ ,pdd.GL_CLOSED_DATE
/*57*/ ,pdd.GL_ENCUMBERED_DATE
/*58*/ ,pdd.GL_ENCUMBERED_PERIOD_NAME
/*59*/ ,pdd.GOVERNMENT_CONTEXT
/*1*/ --GROUP_LINE_ID
/*60*/ , null
/*61*/ , to_date(null)
/*62*/ , null
/*63*/ ,pdd.INVOICE_ADJUSTMENT_FLAG
/*64*/ ,pdd.KANBAN_CARD_ID
/*65*/ ,pdd.LAST_UPDATE_DATE
/*66*/ ,pdd.LAST_UPDATE_LOGIN
/*67*/ ,pdd.LAST_UPDATED_BY
/*68*/ , null
/*69*/ , null
/*70*/ ,pdd.LINE_LOCATION_ID
/*71*/ , null
/*72*/ , null
/*73*/ , null
/*74*/ ,pdd.MRC_ENCUMBERED_AMOUNT
/*75*/ ,pdd.MRC_RATE
/*76*/ ,pdd.MRC_RATE_DATE
/*77*/ ,pdd.MRC_UNENCUMBERED_AMOUNT
/*78*/ ,pdd.NONRECOVERABLE_TAX
/*79*/ ,pdd.OKE_CONTRACT_DELIVERABLE_ID
/*80*/ ,pdd.OKE_CONTRACT_LINE_ID
/*81*/ ,pdd.ORG_ID
/*1*/ --OWNER_ROLE
/*1*/ --OWNER_USER_ID
/*82*/ ,pdd.PO_DISTRIBUTION_ID
/*83*/ ,pdd.PO_HEADER_ID
/*84*/ ,pdd.PO_LINE_ID
/*85*/ ,pdd.PO_RELEASE_ID
/*86*/ ,pdd.PREVENT_ENCUMBRANCE_FLAG
/*87*/ ,pdd.PROGRAM_APPLICATION_ID
/*88*/ ,pdd.PROGRAM_ID
/*89*/ ,pdd.PROGRAM_UPDATE_DATE
/*90*/ ,pdd.PROJECT_ACCOUNTING_CONTEXT
/*91*/ ,pdd.PROJECT_ID
/*92*/ , null
/*93*/ ,pd.QUANTITY_BILLED --updated to draft while sync
/*94*/ ,pdd.QUANTITY_CANCELLED
/*94*/ ,pdd.QUANTITY_DELIVERED
/*96*/ ,pdd.QUANTITY_FINANCED
/*97*/ ,pdd.QUANTITY_ORDERED
/*98*/ ,pdd.QUANTITY_RECOUPED
/*99*/ ,pdd.RATE
/*100*/ ,pdd.RATE_DATE
/*101*/ ,pdd.RECOVERABLE_TAX
/*102*/ ,pdd.RECOVERY_RATE
/*103*/ ,pdd.REQ_DISTRIBUTION_ID
/*104*/ ,pdd.REQ_HEADER_REFERENCE_NUM
/*105*/ ,pdd.REQ_LINE_REFERENCE_NUM
/*106*/ ,pdd.REQUEST_ID
/*107*/ ,pdd.RETAINAGE_RELEASED_AMOUNT
/*108*/ ,pdd.RETAINAGE_WITHHELD_AMOUNT
/*109*/ ,pdd.SET_OF_BOOKS_ID
/*110*/ , null
/*111*/ , null
/*112*/ ,pdd.SOURCE_DISTRIBUTION_ID
/*1*/ --STATUS
/*113*/ ,pdd.TASK_ID
/*1*/ --TAX_ATTRIBUTE_UPDATE_CODE
/*114*/ ,pdd.TAX_RECOVERY_OVERRIDE_FLAG
/*115*/ , null
/*116*/ ,pdd.UNENCUMBERED_AMOUNT
/*117*/ ,pdd.UNENCUMBERED_QUANTITY
/*118*/ ,pdd.USSGL_TRANSACTION_CODE
/*119*/ ,pdd.VARIANCE_ACCOUNT_ID
/*120*/ ,pdd.WIP_ENTITY_ID
/*121*/ ,pdd.WIP_LINE_ID
/*122*/ ,pdd.WIP_OPERATION_SEQ_NUM
/*123*/ ,pdd.WIP_REPETITIVE_SCHEDULE_ID
/*124*/ ,pdd.WIP_RESOURCE_SEQ_NUM
/*pd.po_distribution_id,
pd.quantity_billed,
pd.amount_billed*/
from po_distributions_all pd, po_distributions_draft_all pdd
where pdd.po_distribution_id = pd.po_distribution_id
and pdd.draft_id = p_draft_id
and (pdd.quantity_billed <> pd.quantity_billed or
pdd.amount_billed <> pd.amount_billed);
/*50*/ ,l_shipments.DELETE_FLAG
/*51*/ ,l_shipments.DESCRIPTION
/*52*/ ,l_shipments.DRAFT_ID
/*53*/ ,l_shipments.DROP_SHIP_FLAG
/*54*/ ,l_shipments.ENCUMBER_NOW
/*55*/ ,l_shipments.ENCUMBERED_DATE
/*56*/ ,l_shipments.ENCUMBERED_FLAG
/*57*/ ,l_shipments.END_DATE
/*58*/ ,l_shipments.ENFORCE_SHIP_TO_LOCATION_CODE
/*59*/ ,l_shipments.ESTIMATED_TAX_AMOUNT
/*60*/ ,l_shipments.FINAL_MATCH_FLAG
/*61*/ ,l_shipments.FIRM_DATE
/*62*/ ,l_shipments.FIRM_FLAG
/*63*/ ,l_shipments.FIRM_STATUS_LOOKUP_CODE
/*64*/ ,l_shipments.FOB_LOOKUP_CODE
/*65*/ ,l_shipments.FREIGHT_CARRIER
/*66*/ ,l_shipments.FREIGHT_TERMS_LOOKUP_CODE
/*67*/ ,l_shipments.FROM_DATE
/*68*/ ,l_shipments.FROM_HEADER_ID
/*69*/ ,l_shipments.FROM_LINE_ID
/*70*/ ,l_shipments.FROM_LINE_LOCATION_ID
/*71*/ ,l_shipments.GLOBAL_ATTRIBUTE_CATEGORY
/*72*/ ,l_shipments.GLOBAL_ATTRIBUTE1
/*73*/ ,l_shipments.GLOBAL_ATTRIBUTE10
/*74*/ ,l_shipments.GLOBAL_ATTRIBUTE11
/*75*/ ,l_shipments.GLOBAL_ATTRIBUTE12
/*76*/ ,l_shipments.GLOBAL_ATTRIBUTE13
/*77*/ ,l_shipments.GLOBAL_ATTRIBUTE14
/*78*/ ,l_shipments.GLOBAL_ATTRIBUTE15
/*79*/ ,l_shipments.GLOBAL_ATTRIBUTE16
/*80*/ ,l_shipments.GLOBAL_ATTRIBUTE17
/*81*/ ,l_shipments.GLOBAL_ATTRIBUTE18
/*82*/ ,l_shipments.GLOBAL_ATTRIBUTE19
/*83*/ ,l_shipments.GLOBAL_ATTRIBUTE2
/*84*/ ,l_shipments.GLOBAL_ATTRIBUTE20
/*85*/ ,l_shipments.GLOBAL_ATTRIBUTE3
/*86*/ ,l_shipments.GLOBAL_ATTRIBUTE4
/*87*/ ,l_shipments.GLOBAL_ATTRIBUTE5
/*88*/ ,l_shipments.GLOBAL_ATTRIBUTE6
/*89*/ ,l_shipments.GLOBAL_ATTRIBUTE7
/*90*/ ,l_shipments.GLOBAL_ATTRIBUTE8
/*91*/ ,l_shipments.GLOBAL_ATTRIBUTE9
/*92*/ ,l_shipments.GOVERNMENT_CONTEXT
/*93*/ ,l_shipments.HDR_END_DATE
/*94*/ ,l_shipments.HDR_START_DATE
/*95*/ ,l_shipments.HDR_STYLE_ID
/*96*/ ,l_shipments.INSPECTION_REQUIRED_FLAG
/*97*/ ,l_shipments.INTERFACE_ID
/*98*/ ,l_shipments.INVOICE_CLOSE_TOLERANCE
/*99*/ ,l_shipments.ITEM
/*100*/ ,l_shipments.ITEM_ID
/*101*/ ,l_shipments.ITEM_REVISION
/*102*/ ,l_shipments.LAST_ACCEPT_DATE
/*103*/ ,l_shipments.LAST_UPDATE_DATE
/*104*/ ,l_shipments.LAST_UPDATE_LOGIN
/*105*/ ,l_shipments.LAST_UPDATED_BY
/*106*/ ,l_shipments.LEAD_TIME
/*107*/ ,l_shipments.LEAD_TIME_UNIT
/*108*/ ,l_shipments.LINE_EXPIRATION_DATE
/*109*/ ,l_shipments.LINE_ITEM_ID
/*110*/ ,l_shipments.LINE_LOCATION_ID
/*111*/ ,l_shipments.LINE_ORDER_TYPE_LOOKUP_CODE
/*112*/ ,l_shipments.LINE_PRICE_BREAK_LOOKUP_CODE
/*113*/ ,l_shipments.MANUAL_PRICE_CHANGE_FLAG
/*114*/ ,l_shipments.MATCH_OPTION
/*115*/ ,l_shipments.MATCHING_BASIS
/*116*/ ,l_shipments.NEED_BY_DATE
/*117*/ ,l_shipments.NOTE_TO_RECEIVER
/*118*/ ,l_shipments.ORDER_TYPE_LOOKUP_CODE
/*119*/ ,l_shipments.ORG_ID
/*120*/ ,l_shipments.PAYMENT_TYPE
/*121*/ ,l_shipments.PO_HEADER_ID
/*122*/ ,l_shipments.PO_LINE_ID
/*123*/ ,l_shipments.PO_RELEASE_ID
/*124*/ ,l_shipments.PREFERRED_GRADE
/*125*/ ,l_shipments.PRICE_DISCOUNT
/*126*/ ,l_shipments.PRICE_OVERRIDE
/*127*/ ,l_shipments.PROGRAM_APPLICATION_ID
/*128*/ ,l_shipments.PROGRAM_ID
/*128*/ ,l_shipments.PROGRAM_UPDATE_DATE
/*130*/ ,l_shipments.PROMISED_DATE
/*131*/ ,l_shipments.PURCHASE_BASIS
/*132*/ ,l_shipments.QTY_RCV_EXCEPTION_CODE
/*133*/ ,l_shipments.QTY_RCV_TOLERANCE
/*134*/ ,l_shipments.QUANTITY
/*135*/ ,l_shipments.QUANTITY_ACCEPTED
/*136*/ ,l_shipments.QUANTITY_BILLED
/*137*/ ,l_shipments.QUANTITY_CANCELLED
/*138*/ ,l_shipments.QUANTITY_FINANCED
/*139*/ ,l_shipments.QUANTITY_RECEIVED
/*140*/ ,l_shipments.QUANTITY_RECOUPED
/*141*/ ,l_shipments.QUANTITY_REJECTED
/*142*/ ,l_shipments.QUANTITY_SHIPPED
/*143*/ ,l_shipments.RECEIPT_DAYS_EXCEPTION_CODE
/*144*/ ,l_shipments.RECEIPT_REQUIRED_FLAG
/*145*/ ,l_shipments.RECEIVE_CLOSE_TOLERANCE
/*146*/ ,l_shipments.RECEIVING_ROUTING_ID
/*147*/ ,l_shipments.REQUEST_ID
/*148*/ ,l_shipments.RETAINAGE_RELEASED_AMOUNT
/*149*/ ,l_shipments.RETAINAGE_WITHHELD_AMOUNT
/*150*/ ,l_shipments.RETROACTIVE_DATE
/*151*/ ,l_shipments.SALES_ORDER_UPDATE_DATE
/*152*/ ,l_shipments.SECONDARY_QUANTITY
/*153*/ ,l_shipments.SECONDARY_QUANTITY_ACCEPTED
/*154*/ ,l_shipments.SECONDARY_QUANTITY_CANCELLED
/*155*/ ,l_shipments.SECONDARY_QUANTITY_RECEIVED
/*156*/ ,l_shipments.SECONDARY_QUANTITY_REJECTED
/*157*/ ,l_shipments.SECONDARY_QUANTITY_SHIPPED
/*158*/ ,l_shipments.SECONDARY_UNIT_OF_MEASURE
/*159*/ ,l_shipments.SHIP_TO_LOCATION_ID
/*160*/ ,l_shipments.SHIP_TO_ORGANIZATION_ID
/*161*/ ,l_shipments.SHIP_VIA_LOOKUP_CODE
/*162*/ ,l_shipments.SHIPMENT_CLOSED_DATE
/*163*/ ,l_shipments.SHIPMENT_NUM
/*164*/ ,l_shipments.SHIPMENT_TYPE
/*165*/ ,l_shipments.SOURCE_SHIPMENT_ID
/*166*/ ,l_shipments.START_DATE
/*167*/ ,l_shipments.SUPPLIER_ORDER_LINE_NUMBER
/*168*/ ,l_shipments.TAX_CODE_ID
/*169*/ ,l_shipments.TAX_NAME
/*170*/ ,l_shipments.TAX_USER_OVERRIDE_FLAG
/*171*/ ,l_shipments.TAXABLE_FLAG
/*172*/ ,l_shipments.TERMS_ID
/*173*/ ,l_shipments.TO_DATE
/*174*/ ,l_shipments.TRANSACTION_FLOW_HEADER_ID
/*175*/ ,l_shipments.UNENCUMBERED_QUANTITY
/*176*/ ,l_shipments.UNIT_MEAS_LOOKUP_CODE
/*177*/ ,l_shipments.UNIT_OF_MEASURE_CLASS
/*178*/ ,l_shipments.USSGL_TRANSACTION_CODE
/*179*/ ,l_shipments.VALUE_BASIS
/*180*/ ,l_shipments.VMI_FLAG
/*181*/ ,l_shipments.WORK_APPROVER_ID;
/*35*/ ,l_distributions.DELETE_FLAG
/*36*/ ,l_distributions.DELIVER_TO_LOCATION_ID
/*37*/ ,l_distributions.DELIVER_TO_PERSON_ID
/*38*/ ,l_distributions.DEST_CHARGE_ACCOUNT_ID
/*39*/ ,l_distributions.DEST_VARIANCE_ACCOUNT_ID
/*40*/ ,l_distributions.DESTINATION_CONTEXT
/*41*/ ,l_distributions.DESTINATION_ORGANIZATION_ID
/*42*/ ,l_distributions.DESTINATION_SUBINVENTORY
/*43*/ ,l_distributions.DESTINATION_TYPE_CODE
/*44*/ ,l_distributions.DISTRIBUTION_NUM
/*45*/ ,l_distributions.DISTRIBUTION_TYPE
/*46*/ ,l_distributions.DRAFT_ID
/*47*/ ,l_distributions.ENCUMBERED_AMOUNT
/*48*/ ,l_distributions.ENCUMBERED_FLAG
/*49*/ ,l_distributions.END_ITEM_UNIT_NUMBER
/*50*/ ,l_distributions.EXPENDITURE_ITEM_DATE
/*51*/ ,l_distributions.EXPENDITURE_ORGANIZATION_ID
/*52*/ ,l_distributions.EXPENDITURE_TYPE
/*53*/ ,l_distributions.EXPENSE_ACCRUAL_CODE
/*54*/ ,l_distributions.FAILED_FUNDS_LOOKUP_CODE
/*55*/ ,l_distributions.GL_CANCELLED_DATE
/*56*/ ,l_distributions.GL_CLOSED_DATE
/*57*/ ,l_distributions.GL_ENCUMBERED_DATE
/*58*/ ,l_distributions.GL_ENCUMBERED_PERIOD_NAME
/*59*/ ,l_distributions.GOVERNMENT_CONTEXT
/*60*/ ,l_distributions.HDR_AGENT_ID
/*61*/ ,l_distributions.HEADER_NEED_BY_DATE
/*62*/ ,l_distributions.INTERFACE_ID
/*63*/ ,l_distributions.INVOICE_ADJUSTMENT_FLAG
/*64*/ ,l_distributions.KANBAN_CARD_ID
/*65*/ ,l_distributions.LAST_UPDATE_DATE
/*66*/ ,l_distributions.LAST_UPDATE_LOGIN
/*67*/ ,l_distributions.LAST_UPDATED_BY
/*68*/ ,l_distributions.LINE_ITEM_ID
/*69*/ ,l_distributions.LINE_LINE_TYPE_ID
/*70*/ ,l_distributions.LINE_LOCATION_ID
/*71*/ ,l_distributions.LINE_ORDER_TYPE_LOOKUP_CODE
/*72*/ ,l_distributions.LINE_PURCHASE_BASIS
/*73*/ ,l_distributions.LOC_OUTSOURCED_ASSEMBLY
/*74*/ ,l_distributions.MRC_ENCUMBERED_AMOUNT
/*75*/ ,l_distributions.MRC_RATE
/*76*/ ,l_distributions.MRC_RATE_DATE
/*77*/ ,l_distributions.MRC_UNENCUMBERED_AMOUNT
/*78*/ ,l_distributions.NONRECOVERABLE_TAX
/*79*/ ,l_distributions.OKE_CONTRACT_DELIVERABLE_ID
/*80*/ ,l_distributions.OKE_CONTRACT_LINE_ID
/*81*/ ,l_distributions.ORG_ID
/*82*/ ,l_distributions.PO_DISTRIBUTION_ID
/*83*/ ,l_distributions.PO_HEADER_ID
/*84*/ ,l_distributions.PO_LINE_ID
/*85*/ ,l_distributions.PO_RELEASE_ID
/*86*/ ,l_distributions.PREVENT_ENCUMBRANCE_FLAG
/*87*/ ,l_distributions.PROGRAM_APPLICATION_ID
/*88*/ ,l_distributions.PROGRAM_ID
/*89*/ ,l_distributions.PROGRAM_UPDATE_DATE
/*90*/ ,l_distributions.PROJECT_ACCOUNTING_CONTEXT
/*91*/ ,l_distributions.PROJECT_ID
/*92*/ ,l_distributions.PROMISED_DATE
/*93*/ ,l_distributions.QUANTITY_BILLED
/*94*/ ,l_distributions.QUANTITY_CANCELLED
/*95*/ ,l_distributions.QUANTITY_DELIVERED
/*96*/ ,l_distributions.QUANTITY_FINANCED
/*97*/ ,l_distributions.QUANTITY_ORDERED
/*98*/ ,l_distributions.QUANTITY_RECOUPED
/*99*/ ,l_distributions.RATE
/*100*/ ,l_distributions.RATE_DATE
/*101*/ ,l_distributions.RECOVERABLE_TAX
/*102*/ ,l_distributions.RECOVERY_RATE
/*103*/ ,l_distributions.REQ_DISTRIBUTION_ID
/*104*/ ,l_distributions.REQ_HEADER_REFERENCE_NUM
/*105*/ ,l_distributions.REQ_LINE_REFERENCE_NUM
/*106*/ ,l_distributions.REQUEST_ID
/*107*/ ,l_distributions.RETAINAGE_RELEASED_AMOUNT
/*108*/ ,l_distributions.RETAINAGE_WITHHELD_AMOUNT
/*109*/ ,l_distributions.SET_OF_BOOKS_ID
/*110*/ ,l_distributions.SHIP_TO_ORGANIZATION_ID
/*111*/ ,l_distributions.SHIP_VALUE_BASIS
/*112*/ ,l_distributions.SOURCE_DISTRIBUTION_ID
/*113*/ ,l_distributions.TASK_ID
/*114*/ ,l_distributions.TAX_RECOVERY_OVERRIDE_FLAG
/*115*/ ,l_distributions.TRANSACTION_FLOW_HEADER_ID
/*116*/ ,l_distributions.UNENCUMBERED_AMOUNT
/*117*/ ,l_distributions.UNENCUMBERED_QUANTITY
/*118*/ ,l_distributions.USSGL_TRANSACTION_CODE
/*119*/ ,l_distributions.VARIANCE_ACCOUNT_ID
/*120*/ ,l_distributions.WIP_ENTITY_ID
/*121*/ ,l_distributions.WIP_LINE_ID
/*122*/ ,l_distributions.WIP_OPERATION_SEQ_NUM
/*123*/ ,l_distributions.WIP_REPETITIVE_SCHEDULE_ID
/*124*/ ,l_distributions.WIP_RESOURCE_SEQ_NUM;
--update
FORALL I in l_shipments.line_location_id.first
..l_shipments.line_location_id.last
UPDATE po_line_locations_draft_all
set quantity_received = l_shipments.quantity_received(I),
quantity_billed = l_shipments.quantity_billed(I),
amount_received = l_shipments.amount_received(I),
amount_billed = l_shipments.amount_billed(I)
where draft_id = p_draft_id
and line_location_id = l_shipments.line_location_id(I);
'Updated '||sql%rowcount||' no of shipments');
UPDATE po_distributions_draft_all
set quantity_billed = l_distributions.quantity_billed(I),
amount_billed = l_distributions.amount_billed(I)
where draft_id = p_draft_id
and po_distribution_id = l_distributions.po_distribution_id(I);
'Updated '||sql%rowcount||' no of distributions');
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = p_draft_id;
SELECT TYPE_LOOKUP_CODE, STYLE_ID
INTO l_document_type_code, l_document_style_id
FROM po_headers_all
WHERE PO_HEADER_ID = l_po_header_id;
UPDATE po_drafts
SET revision_num = NULL
WHERE draft_id = p_draft_id
AND revision_num = -1;
l_delete_flag VARCHAR2(1);
l_delete_flag_tbl PO_TBL_VARCHAR1;
l_delete_flag_tbl:=Po_Tbl_Varchar1();
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = p_draft_id;
/*amount limit and old amount limit are the concurrent update attributes for header*/
SELECT phd.revision_num,
pha.revision_num,
pha.amount_limit,
phd.amount_limit,
phd.old_amount_limit,
phd.change_status
INTO l_header_draft_rev_num,
l_header_base_rev_num,
l_base_amount_limit,
l_draft_amount_limit,
l_draft_old_amount_limit,
l_change_status
FROM po_headers_all pha
,po_headers_draft_all phd
WHERE pha.po_header_id = phd.po_header_id
AND phd.draft_id = p_draft_id
AND pha.po_header_id = l_po_header_id;
SELECT revision_num
INTO l_draft_rev_num
FROM po_drafts
WHERE draft_id = p_draft_id;
SELECT lock_type
INTO l_lock_type
FROM po_entity_locks
WHERE lock_by_draft_id = p_draft_id
AND entity_name = PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY
AND entity_pk1 = l_po_header_id;
UPDATE po_headers_draft_all pohd
SET ( pohd.amount_limit ,
pohd.user_document_status,
pohd.vendor_site_id,
pohd.vendor_contact_id,
pohd.supplier_notif_method,
pohd.fax,
pohd.email_address,
pohd.clm_external_idv,
pohd.clm_vendor_offer_number,
pohd.clm_effective_date,
pohd.agent_id,
pohd.clm_award_administrator,
pohd.comments,
pohd.confirming_order_flag,
pohd.terms_id,
pohd.ship_to_location_id,
pohd.bill_to_location_id,
pohd.ship_via_lookup_code,
pohd.freight_terms_lookup_code,
pohd.fob_lookup_code,
pohd.shipping_control,
pohd.note_to_vendor,
pohd.note_to_receiver,
pohd.blanket_total_amount,
pohd.price_update_tolerance,
pohd.start_date,
pohd.end_date,
pohd.clm_max_order_amount,
pohd.clm_min_order_amount,
pohd.clm_min_guarantee_award_amt,
pohd.clm_min_guar_award_amt_percent,
pohd.auto_sourcing_flag,
pohd.update_sourcing_rules_flag,
pohd.retro_price_apply_updates_flag,
pohd.retro_price_comm_updates_flag,
pohd.attribute_category ,
pohd.attribute1 ,
pohd.attribute2 ,
pohd.attribute3 ,
pohd.attribute4 ,
pohd.attribute5 ,
pohd.attribute6 ,
pohd.attribute7 ,
pohd.attribute8 ,
pohd.attribute9 ,
pohd.attribute10 ,
pohd.attribute11 ,
pohd.attribute12 ,
pohd.attribute13 ,
pohd.attribute14 ,
pohd.attribute15,
pohd.umbrella_program_id,
pohd.revision_num,
pohd.clm_payment_instr_code) -- Added for reverting PI in headers
= ( SELECT poh.amount_limit,
poh.user_document_status,
poh.vendor_site_id,
poh.vendor_contact_id,
poh.supplier_notif_method,
poh.fax,
poh.email_address,
poh.clm_external_idv,
poh.clm_vendor_offer_number,
poh.clm_effective_date,
poh.agent_id,
poh.clm_award_administrator,
poh.comments,
poh.confirming_order_flag,
poh.terms_id,
poh.ship_to_location_id,
poh.bill_to_location_id,
poh.ship_via_lookup_code,
poh.freight_terms_lookup_code,
poh.fob_lookup_code,
poh.shipping_control,
poh.note_to_vendor,
poh.note_to_receiver,
poh.blanket_total_amount,
poh.price_update_tolerance,
poh.start_date,
poh.end_date,
poh.clm_max_order_amount,
poh.clm_min_order_amount,
poh.clm_min_guarantee_award_amt,
poh.clm_min_guar_award_amt_percent,
poh.auto_sourcing_flag,
poh.update_sourcing_rules_flag,
poh.retro_price_apply_updates_flag,
poh.retro_price_comm_updates_flag,
poh.attribute_category ,
poh.attribute1 ,
poh.attribute2 ,
poh.attribute3 ,
poh.attribute4 ,
poh.attribute5 ,
poh.attribute6 ,
poh.attribute7 ,
poh.attribute8 ,
poh.attribute9 ,
poh.attribute10 ,
poh.attribute11 ,
poh.attribute12 ,
poh.attribute13 ,
poh.attribute14 ,
poh.attribute15 ,
poh.umbrella_program_id,
poh.revision_num,
poh.clm_payment_instr_code -- Added for reverting PI in headers
FROM po_headers_all poh
WHERE poh.po_header_id = l_po_header_id)
WHERE pohd.po_header_id=l_po_header_id
AND pohd.draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
SELECT org_assignment_id,
p_draft_id,
'N',
change_status
BULK COLLECT INTO l_org_assignment_id_tbl,
l_draft_id_tbl,
l_delete_flag_tbl,
l_change_status_tbl
FROM po_ga_org_assign_draft
WHERE po_header_id=l_po_header_id
AND draft_id = p_draft_id ;
DELETE FROM po_ga_org_assign_draft pgoad
WHERE pgoad.org_assignment_id = l_org_assignment_id_tbl(i)
AND pgoad.draft_id = p_draft_id ;
p_delete_flag_tbl => l_delete_flag_tbl,
x_record_already_exist_tbl => l_record_already_exist_tbl
);
UPDATE po_ga_org_assign_draft pgoad
SET pgoad.change_status = l_change_status_tbl(i)
WHERE pgoad.org_assignment_id = l_org_assignment_id_tbl(i)
AND pgoad.draft_id = p_draft_id ;
l_draft_id_tbl.DELETE;
l_delete_flag_tbl.DELETE;
l_change_status_tbl.DELETE;
SELECT notification_id,
p_draft_id,
'N',
change_status
BULK COLLECT INTO l_notification_id_tbl,
l_draft_id_tbl,
l_delete_flag_tbl,
l_change_status_tbl
FROM po_notification_ctrl_draft
WHERE po_header_id=l_po_header_id
AND draft_id = p_draft_id ;
DELETE FROM po_notification_ctrl_draft pncd
WHERE pncd.notification_id=l_notification_id_tbl(i)
AND pncd.draft_id = p_draft_id ;
p_delete_flag_tbl => l_delete_flag_tbl,
x_record_already_exist_tbl => l_record_already_exist_tbl
);
UPDATE po_notification_ctrl_draft pncd
SET pncd.change_status = l_change_status_tbl(i)
WHERE pncd.notification_id=l_notification_id_tbl(i)
AND pncd.draft_id = p_draft_id ;
/*we need to update the delta values for the concurrent update attributes in case of both shared and partial lock*/
IF l_lock_type IN ('S','P') THEN
-- Update only if either of base/drat amount limit is not null
IF l_base_amount_limit IS NOT NULL OR l_draft_amount_limit IS NOT NULL THEN
UPDATE po_headers_draft_all
SET old_amount_limit = l_base_amount_limit,
amount_limit = NVL(l_base_amount_limit, 0) +
( NVL(l_draft_amount_limit, 0) -
NVL(l_draft_old_amount_limit, 0) )
WHERE po_header_id = l_po_header_id
AND draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'updated old_amount_limit', l_base_amount_limit);
PO_LOG.stmt(d_module,d_position ,'updated amount_limit', l_base_amount_limit +
(l_draft_amount_limit-l_draft_old_amount_limit));
/*we need to update revision_num , change_status*/
UPDATE po_headers_draft_all
SET revision_num = l_header_base_rev_num,
change_status = l_change_status
WHERE po_header_id = l_po_header_id
AND draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'updated revision_num', l_header_base_rev_num);
l_delete_flag_tbl PO_TBL_VARCHAR1;
l_delete_flag_tbl:=Po_Tbl_Varchar1();
SELECT pla.revision_num,
pla.quantity,
pla.amount,
plda.quantity,
plda.old_quantity ,
plda.amount,
plda.old_amount,
plda.po_line_id,
plda.CHANGE_STATUS,
pla.CLM_UNDEF_FLAG,--UCA Project
pla.CLM_APPROVED_UNDEF_AMOUNT,--UCA Project
plda.CLM_UNDEF_ACTION_CODE, --UCA Project
plda.CLM_UNDEF_FLAG --bug 16451273
BULK COLLECT INTO l_header_base_rev_num,
l_base_quantity_tbl,
l_base_amount_tbl,
l_draft_quantity_tbl,
l_draft_old_quantity_tbl,
l_draft_amount_tbl,
l_draft_old_amount_tbl,
l_po_line_id_tbl,
l_change_status_lines_tbl,
l_base_CLM_UNDEF_FLAG_tbl, --UCA Project
l_base_CLM_APP_UNDEF_AMT_tbl,--UCA Project
l_draft_CLM_UNDEF_ACTION_CODE,--UCA Project
l_draft_CLM_UNDEF_FLAG_tbl --bug 16451273
FROM po_lines_all pla,
po_lines_draft_all plda
WHERE pla.po_line_id = plda.po_line_id
AND plda.draft_id = p_draft_id
AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
AND NOT EXISTS (SELECT 'F locks'
FROM po_entity_locks pel
WHERE pel.entity_pk1 = plda.po_line_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_LINE_ENTITY
AND pel.lock_by_draft_id = plda.draft_id
AND pel.lock_type = 'F');
SELECT plda.po_line_id,
plda.draft_id,
'N'
BULK COLLECT INTO l_po_line_s_no_lck_tbl,
l_draft_id_tbl,
l_delete_flag_tbl
FROM po_lines_all pla,
po_lines_draft_all plda
WHERE plda.draft_id = p_draft_id
AND pla.po_line_id = plda.po_line_id
AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
AND NOT EXISTS (SELECT 'F/P locks'
FROM po_entity_locks pel
WHERE pel.entity_pk1 = plda.po_line_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_LINE_ENTITY
AND pel.lock_by_draft_id = plda.draft_id
AND pel.lock_type IN ('F', 'P'));
PO_LOG.stmt(d_module,d_position ,'Delete Flag Tbl', l_delete_flag_tbl);
SELECT plda.po_line_id
BULK COLLECT
INTO l_po_line_s_no_p_lck_tbl
FROM po_lines_draft_all plda,
po_lines_all pla
WHERE plda.draft_id = p_draft_id
AND pla.po_line_id = plda.po_line_id
AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
AND NOT EXISTS (SELECT 'F locks'
FROM po_entity_locks pel
WHERE pel.entity_pk1 = plda.po_line_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_LINE_ENTITY
AND pel.lock_by_draft_id = plda.draft_id
AND pel.lock_type ='F');
DELETE FROM po_lines_draft_all plda
WHERE plda.po_line_id = l_po_line_s_no_lck_tbl(i)
AND plda.draft_id=p_draft_id;
PO_LOG.stmt(d_module,d_position ,'Deleted po_line_ids for S and No locks',SQL%ROWCOUNT);
p_delete_flag_tbl => l_delete_flag_tbl,
x_record_already_exist_tbl => l_record_already_exists
);
UPDATE po_lines_draft_all
SET old_quantity = l_base_quantity_tbl(i)
,quantity = l_base_quantity_tbl(i) + (l_draft_quantity_tbl(i)-l_draft_old_quantity_tbl(i))
,old_amount = l_base_amount_tbl(i)
,amount = l_base_amount_tbl(i) + (l_draft_amount_tbl(i) - l_draft_old_amount_tbl(i))
WHERE po_line_id = l_po_line_id_tbl(i)
AND draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'update po_lines_draft all for all locks excluding F lock',SQL%ROWCOUNT);
l_draft_id_tbl.DELETE;
l_delete_flag_tbl.DELETE;
SELECT pavd.attribute_values_id,
p_draft_id,
'N',
change_status
BULK COLLECT INTO l_attribute_values_id_tbl,
l_draft_id_tbl,
l_delete_flag_tbl,
l_change_status_tbl
FROM po_attribute_values_draft pavd
WHERE pavd.po_line_id in ( select * from table(l_po_line_s_no_lck_tbl))
AND pavd.draft_id= p_draft_id;
DELETE FROM po_attribute_values_draft pavd
WHERE pavd.attribute_values_id=l_attribute_values_id_tbl(i)
AND pavd.draft_id = p_draft_id ;
p_delete_flag_tbl => l_delete_flag_tbl,
x_record_already_exist_tbl => l_record_already_exists
);
UPDATE po_attribute_values_draft pavd
SET pavd.change_status = l_change_status_tbl(i)
WHERE pavd.attribute_values_id=l_attribute_values_id_tbl(i)
AND pavd.draft_id = p_draft_id ;
l_draft_id_tbl.DELETE;
l_delete_flag_tbl.DELETE;
l_change_status_tbl.DELETE;
SELECT pavtd.attribute_values_tlp_id,
p_draft_id,
'N',
change_status
BULK COLLECT INTO l_attribute_values_tlp_id_tbl,
l_draft_id_tbl,
l_delete_flag_tbl,
l_change_status_tbl
FROM po_attribute_values_tlp_draft pavtd
WHERE pavtd.po_line_id in ( select * from table(l_po_line_s_no_lck_tbl))
AND pavtd.draft_id = p_draft_id;
DELETE FROM po_attribute_values_tlp_draft pavd
WHERE pavd.attribute_values_tlp_id=l_attribute_values_tlp_id_tbl(i)
AND pavd.draft_id = p_draft_id ;
p_delete_flag_tbl => l_delete_flag_tbl,
x_record_already_exist_tbl => l_record_already_exists
);
UPDATE po_attribute_values_tlp_draft pavd
SET pavd.change_status = l_change_status_tbl(i)
WHERE pavd.attribute_values_tlp_id=l_attribute_values_tlp_id_tbl(i)
AND pavd.draft_id = p_draft_id ;
l_draft_id_tbl.DELETE;
l_delete_flag_tbl.DELETE;
l_change_status_tbl.DELETE;
SELECT ppdr.price_differential_id,
p_draft_id,
'N',
change_status
BULK COLLECT INTO l_price_differential_id_tbl,
l_draft_id_tbl,
l_delete_flag_tbl,
l_change_status_tbl
FROM po_price_diff_draft ppdr
WHERE ppdr.entity_id in ( select * from table(l_po_line_s_no_lck_tbl))
AND ppdr.draft_id = p_draft_id;
DELETE FROM po_price_diff_draft ppdr
WHERE ppdr.price_differential_id=l_price_differential_id_tbl(i)
AND ppdr.draft_id = p_draft_id ;
p_delete_flag_tbl => l_delete_flag_tbl,
x_record_already_exist_tbl => l_record_already_exists
);
UPDATE po_price_diff_draft ppdr
SET ppdr.change_status = l_change_status_tbl(i)
WHERE ppdr.price_differential_id=l_price_differential_id_tbl(i)
AND ppdr.draft_id = p_draft_id ;
UPDATE po_lines_draft_all
SET revision_num=l_header_base_rev_num(i),
change_status = l_change_status_lines_tbl(i)
WHERE po_line_id = l_po_line_id_tbl(i)
AND draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'Number Of Rows Updated',SQL%ROWCOUNT);
SELECT Nvl(po_core_s.Retrieveoptionvalue(org_id,
po_core_s.g_undef_cont_act_col), 'N')
INTO l_isucaenabled
FROM po_lines_draft_all
WHERE draft_id = p_draft_id
AND rownum = 1;
UPDATE po_lines_draft_all
SET clm_approved_undef_amount = nvl(l_base_clm_app_undef_amt_tbl(i),0)
+ nvl((SELECT undef_amount
FROM po_line_ucas
WHERE po_line_id = L_po_line_id_tbl(i)
AND undef_draft_id = p_draft_id),0)
- (SELECT Nvl(SUM(undef_amount), 0)
FROM po_line_ucas
WHERE po_line_id = L_po_line_id_tbl(i)
AND def_draft_id = p_draft_id),
clm_undef_flag = (SELECT Nvl(Max('Y'), l_base_clm_undef_flag_tbl(i))
FROM po_line_ucas
WHERE po_line_id = l_po_line_id_tbl(i)
AND undef_draft_id = p_draft_id
AND def_draft_id IS NULL),
clm_undef_action_code = l_draft_CLM_UNDEF_ACTION_CODE(i)
WHERE po_line_id = l_po_line_id_tbl(i)
AND draft_id = p_draft_id
AND (l_base_clm_undef_flag_tbl(i) = 'Y' OR
l_draft_CLM_UNDEF_FLAG_tbl(i) = 'Y'); --bug 16451273
UPDATE po_lines_draft_all
SET clm_uda_pricing_total =
CASE
WHEN NVL(cost_constraint,'X') IN ('NC','NSP')
THEN 0
ELSE
CASE
WHEN matching_basis = 'QUANTITY'
THEN quantity*unit_price - Nvl(clm_approved_undef_amount,0)
ELSE amount - NVL(clm_approved_undef_amount,0)
END
END
WHERE po_line_id = l_po_line_id_tbl(i)
AND draft_id = p_draft_id
AND order_type_lookup_code in('AMOUNT', 'FIXED PRICE');--bug 16451273
l_delete_flag_tbl PO_TBL_VARCHAR1;
l_delete_flag_tbl:=Po_Tbl_Varchar1();
SELECT
pla.revision_num,
pla.quantity,
pla.amount,
plda.quantity,
plda.old_quantity ,
plda.amount,
plda.old_amount,
plda.line_location_id,
plda.change_status
BULK COLLECT INTO
l_header_base_rev_num,
l_base_quantity_tbl,
l_base_amount_tbl,
l_draft_quantity_tbl,
l_draft_old_quantity_tbl,
l_draft_amount_tbl,
l_draft_old_amount_tbl,
l_line_location_tbl,
l_change_status_tbl
FROM po_line_locations_all pla
,po_line_locations_draft_all plda
WHERE pla.line_location_id = plda.line_location_id
AND plda.draft_id = p_draft_id
AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
AND NOT EXISTS (SELECT 'F locks'
FROM po_entity_locks pel
WHERE pel.entity_pk1 = plda.line_location_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_SHIPMENT_ENTITY
AND pel.lock_by_draft_id = plda.draft_id
AND pel.lock_type = 'F')
AND pla.shipment_type <> 'PRICE BREAK';
SELECT plda.line_location_id
,plda.draft_id
,'N'
BULK COLLECT INTO l_po_line_loc_s_no_lck_tbl
,l_draft_id_tbl
,l_delete_flag_tbl
FROM po_line_locations_draft_all plda,
po_line_locations_all pla
WHERE plda.draft_id = p_draft_id
AND pla.line_location_id = plda.line_location_id
AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
AND NOT EXISTS (SELECT 'P/F locks'
FROM po_entity_locks pel
WHERE pel.entity_pk1 = plda.line_location_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_SHIPMENT_ENTITY
AND pel.lock_by_draft_id = plda.draft_id
AND pel.lock_type IN ('F','P'))
AND pla.shipment_type <> 'PRICE BREAK';
PO_LOG.stmt(d_module,d_position ,'Delete Flag Tbl', l_delete_flag_tbl);
DELETE FROM po_line_locations_draft_all plda
WHERE plda.line_location_id = l_po_line_loc_s_no_lck_tbl(i)
AND plda.draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'Deleted line_location_ids having S and No locks',SQL%ROWCOUNT);
p_delete_flag_tbl => l_delete_flag_tbl,
x_record_already_exist_tbl => l_record_already_exists
);
UPDATE po_line_locations_draft_all
SET old_quantity = l_base_quantity_tbl(i)
,quantity = l_base_quantity_tbl(i) + (l_draft_quantity_tbl(i)-l_draft_old_quantity_tbl(i))
,old_amount = l_base_amount_tbl(i)
,amount = l_base_amount_tbl(i) + (l_draft_amount_tbl(i) - l_draft_old_amount_tbl(i))
WHERE line_location_id = l_line_location_tbl(i)
AND draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'update po_lines_draft all for all locks excluding F lock',SQL%ROWCOUNT);
UPDATE po_line_locations_draft_all
SET revision_num=l_header_base_rev_num(i),
change_status = l_change_status_tbl(i)
WHERE line_location_id = l_line_location_tbl(i)
AND draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'Number of Rows Updated',SQL%ROWCOUNT);
l_delete_flag_tbl PO_TBL_VARCHAR1;
l_delete_flag_tbl:=Po_Tbl_Varchar1();
SELECT pla.revision_num,
pla.quantity_ordered,
pla.amount_ordered,
plda.quantity_ordered ,
plda.old_quantity_ordered ,
plda.amount_ordered,
plda.old_amount_ordered,
plda.po_distribution_id,
plda.change_status
BULK COLLECT INTO
l_header_base_rev_num,
l_base_quantity_tbl,
l_base_amount_tbl,
l_draft_quantity_tbl,
l_draft_old_quantity_tbl,
l_draft_amount_tbl,
l_draft_old_amount_tbl,
l_po_distribution_tbl,
l_change_status_tbl
FROM po_distributions_all pla
,po_distributions_draft_all plda
WHERE pla.po_distribution_id = plda.po_distribution_id
AND plda.draft_id = p_draft_id
AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
AND NOT EXISTS (SELECT 'F locks'
FROM po_entity_locks pel
WHERE pel.entity_pk1 = plda.po_distribution_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_DIST_ENTITY
AND pel.lock_by_draft_id = plda.draft_id
AND pel.lock_type = 'F')
AND pla.distribution_type <> 'AGREEMENT';
SELECT plda.po_distribution_id
,p_draft_id
,'N'
BULK COLLECT INTO l_po_dist_s_no_lck_tbl
,l_draft_id_tbl
,l_delete_flag_tbl
FROM po_distributions_draft_all plda,
po_distributions_all pla
WHERE plda.draft_id = p_draft_id
AND pla.po_distribution_id = plda.po_distribution_id
AND NVL(pla.revision_num, 0) > NVL(plda.revision_num, 0)
AND NOT EXISTS (SELECT 'P/F locks'
FROM po_entity_locks pel
WHERE pel.entity_pk1 = plda.po_distribution_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_DIST_ENTITY
AND pel.lock_by_draft_id = plda.draft_id
AND pel.lock_type IN ('F','P'))
AND pla.distribution_type <> 'AGREEMENT';
PO_LOG.stmt(d_module,d_position ,'Delete Flag Tbl', l_delete_flag_tbl);
DELETE FROM po_distributions_draft_all plda
WHERE plda.po_distribution_id=l_po_dist_s_no_lck_tbl(i)
AND plda.draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'Deleted po_dist_ids having S and No locks',SQL%ROWCOUNT);
p_delete_flag_tbl => l_delete_flag_tbl,
x_record_already_exist_tbl => l_record_already_exists
);
UPDATE po_distributions_draft_all
SET old_quantity_ordered = l_base_quantity_tbl(i)
,quantity_ordered = l_base_quantity_tbl(i) + (l_draft_quantity_tbl(i)-l_draft_old_quantity_tbl(i))
,old_amount_ordered = l_base_amount_tbl(i)
,amount_ordered = l_base_amount_tbl(i) + (l_draft_amount_tbl(i) - l_draft_old_amount_tbl(i))
,revision_num = l_header_base_rev_num(i),
change_status= l_change_status_tbl(i)
WHERE po_distribution_id = l_po_distribution_tbl(i)
AND draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'update po_distributions_draft_all for all locks excluding F lock');
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = p_draft_id;
(SELECT phb1.extension_id ,
phb1.attr_group_id ,
phb1.po_header_id ,
phb1.data_level_id ,
phb1.pk1_value ,
phb1.pk2_value ,
phb1.pk3_value ,
phb1.pk4_value ,
phb1.pk5_value ,
phb1.last_update_date ,
phb1.last_updated_by ,
phb1.last_update_login ,
phb1.created_by ,
phb1.creation_date ,
phb1.c_ext_attr1 ,
phb1.c_ext_attr2 ,
phb1.c_ext_attr3 ,
phb1.c_ext_attr4 ,
phb1.c_ext_attr5 ,
phb1.c_ext_attr6 ,
phb1.c_ext_attr7 ,
phb1.c_ext_attr8 ,
phb1.c_ext_attr9 ,
phb1.c_ext_attr10 ,
phb1.c_ext_attr11 ,
phb1.c_ext_attr12 ,
phb1.c_ext_attr13 ,
phb1.c_ext_attr14 ,
phb1.c_ext_attr15 ,
phb1.c_ext_attr16 ,
phb1.c_ext_attr17 ,
phb1.c_ext_attr18 ,
phb1.c_ext_attr19 ,
phb1.c_ext_attr20 ,
phb1.c_ext_attr21 ,
phb1.c_ext_attr22 ,
phb1.c_ext_attr23 ,
phb1.c_ext_attr24 ,
phb1.c_ext_attr25 ,
phb1.c_ext_attr26 ,
phb1.c_ext_attr27 ,
phb1.c_ext_attr28 ,
phb1.c_ext_attr29 ,
phb1.c_ext_attr30 ,
phb1.c_ext_attr31 ,
phb1.c_ext_attr32 ,
phb1.c_ext_attr33 ,
phb1.c_ext_attr34 ,
phb1.c_ext_attr35 ,
phb1.c_ext_attr36 ,
phb1.c_ext_attr37 ,
phb1.c_ext_attr38 ,
phb1.c_ext_attr39 ,
phb1.c_ext_attr40 ,
phb1.n_ext_attr1 ,
phb1.n_ext_attr2 ,
phb1.n_ext_attr3 ,
phb1.n_ext_attr4 ,
phb1.n_ext_attr5 ,
phb1.n_ext_attr6 ,
phb1.n_ext_attr7 ,
phb1.n_ext_attr8 ,
phb1.n_ext_attr9 ,
phb1.n_ext_attr10 ,
phb1.n_ext_attr11 ,
phb1.n_ext_attr12 ,
phb1.n_ext_attr13 ,
phb1.n_ext_attr14 ,
phb1.n_ext_attr15 ,
phb1.n_ext_attr16 ,
phb1.n_ext_attr17 ,
phb1.n_ext_attr18 ,
phb1.n_ext_attr19 ,
phb1.n_ext_attr20 ,
phb1.uom_ext_attr1 ,
phb1.uom_ext_attr2 ,
phb1.uom_ext_attr3 ,
phb1.uom_ext_attr4 ,
phb1.uom_ext_attr5 ,
phb1.uom_ext_attr6 ,
phb1.uom_ext_attr7 ,
phb1.uom_ext_attr8 ,
phb1.uom_ext_attr9 ,
phb1.uom_ext_attr10 ,
phb1.uom_ext_attr11 ,
phb1.uom_ext_attr12 ,
phb1.uom_ext_attr13 ,
phb1.uom_ext_attr14 ,
phb1.uom_ext_attr15 ,
phb1.uom_ext_attr16 ,
phb1.uom_ext_attr17 ,
phb1.uom_ext_attr18 ,
phb1.uom_ext_attr19 ,
phb1.uom_ext_attr20 ,
phb1.d_ext_attr1 ,
phb1.d_ext_attr2 ,
phb1.d_ext_attr3 ,
phb1.d_ext_attr4 ,
phb1.d_ext_attr5 ,
phb1.d_ext_attr6 ,
phb1.d_ext_attr7 ,
phb1.d_ext_attr8 ,
phb1.d_ext_attr9 ,
phb1.d_ext_attr10 ,
phb1.uda_template_id ,
phb1.draft_id ,
phb1.revision_num
FROM po_headers_all_ext_b phb1
WHERE phb1.draft_id = -1
AND phb1.po_header_id = l_po_header_id
-- exclude Mod specific and doc numbering attribute groups
AND NOT EXISTS (SELECT 'Mod specific or doc numbering attribute groups'
FROM po_uda_ag_template_usages usg
WHERE usg.template_id = phb1.uda_template_id
AND usg.attribute_group_id = phb1.attr_group_id
AND usg.attribute_category IN ('DOCUMENT_NUMBERING', 'MODIFICATIONS')
)
AND NOT EXISTS (SELECT 'only S or Null Locks'
FROM po_entity_locks pel
WHERE pel.lock_type IN ('P','F')
AND pel.entity_pk1= phb1.po_header_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY
AND pel.lock_by_draft_id = p_draft_id )
-- exclude multi-row attribute groups
AND EXISTS (SELECT 'Single row attr grp'
FROM ego_fnd_dsc_flx_ctx_ext ag
WHERE phb1.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'N'
)
) phb
ON ( phb.attr_group_id = phbd.attr_group_id
AND phbd.po_header_id = l_po_header_id
AND phbd.draft_id = p_draft_id
)
WHEN MATCHED THEN
UPDATE
SET
phbd.c_ext_attr1 = phb.c_ext_attr1,
phbd.c_ext_attr2 = phb.c_ext_attr2 ,
phbd.c_ext_attr3 = phb.c_ext_attr3 ,
phbd.c_ext_attr4 = phb.c_ext_attr4 ,
phbd.c_ext_attr5 = phb.c_ext_attr5 ,
phbd.c_ext_attr6 = phb.c_ext_attr6 ,
phbd.c_ext_attr7 = phb.c_ext_attr7 ,
phbd.c_ext_attr8 = phb.c_ext_attr8 ,
phbd.c_ext_attr9 = phb.c_ext_attr9 ,
phbd.c_ext_attr10 = phb.c_ext_attr10 ,
phbd.c_ext_attr11 = phb.c_ext_attr11 ,
phbd.c_ext_attr12 = phb.c_ext_attr12 ,
phbd.c_ext_attr13 = phb.c_ext_attr13 ,
phbd.c_ext_attr14 = phb.c_ext_attr14 ,
phbd.c_ext_attr15 = phb.c_ext_attr15 ,
phbd.c_ext_attr16 = phb.c_ext_attr16 ,
phbd.c_ext_attr17 = phb.c_ext_attr17 ,
phbd.c_ext_attr18 = phb.c_ext_attr18 ,
phbd.c_ext_attr19 = phb.c_ext_attr19 ,
phbd.c_ext_attr20 = phb.c_ext_attr20 ,
phbd.c_ext_attr21 = phb.c_ext_attr21 ,
phbd.c_ext_attr22 = phb.c_ext_attr22 ,
phbd.c_ext_attr23 = phb.c_ext_attr23 ,
phbd.c_ext_attr24 = phb.c_ext_attr24 ,
phbd.c_ext_attr25 = phb.c_ext_attr25 ,
phbd.c_ext_attr26 = phb.c_ext_attr26 ,
phbd.c_ext_attr27 = phb.c_ext_attr27 ,
phbd.c_ext_attr28 = phb.c_ext_attr28 ,
phbd.c_ext_attr29 = phb.c_ext_attr29 ,
phbd.c_ext_attr30 = phb.c_ext_attr30 ,
phbd.c_ext_attr31 = phb.c_ext_attr31 ,
phbd.c_ext_attr32 = phb.c_ext_attr32 ,
phbd.c_ext_attr33 = phb.c_ext_attr33 ,
phbd.c_ext_attr34 = phb.c_ext_attr34 ,
phbd.c_ext_attr35 = phb.c_ext_attr35 ,
phbd.c_ext_attr36 = phb.c_ext_attr36 ,
phbd.c_ext_attr37 = phb.c_ext_attr37 ,
phbd.c_ext_attr38 = phb.c_ext_attr38 ,
phbd.c_ext_attr39 = phb.c_ext_attr39 ,
phbd.c_ext_attr40 = phb.c_ext_attr40 ,
phbd.n_ext_attr1 = phb.n_ext_attr1 ,
phbd.n_ext_attr2 = phb.n_ext_attr2 ,
phbd.n_ext_attr3 = phb.n_ext_attr3 ,
phbd.n_ext_attr4 = phb.n_ext_attr4 ,
phbd.n_ext_attr5 = phb.n_ext_attr5 ,
phbd.n_ext_attr6 = phb.n_ext_attr6 ,
phbd.n_ext_attr7 = phb.n_ext_attr7 ,
phbd.n_ext_attr8 = phb.n_ext_attr8 ,
phbd.n_ext_attr9 = phb.n_ext_attr9 ,
phbd.n_ext_attr10 = phb.n_ext_attr10 ,
phbd.n_ext_attr11 = phb.n_ext_attr11 ,
phbd.n_ext_attr12 = phb.n_ext_attr12 ,
phbd.n_ext_attr13 = phb.n_ext_attr13 ,
phbd.n_ext_attr14 = phb.n_ext_attr14 ,
phbd.n_ext_attr15 = phb.n_ext_attr15 ,
phbd.n_ext_attr16 = phb.n_ext_attr16 ,
phbd.n_ext_attr17 = phb.n_ext_attr17 ,
phbd.n_ext_attr18 = phb.n_ext_attr18 ,
phbd.n_ext_attr19 = phb.n_ext_attr19 ,
phbd.n_ext_attr20 = phb.n_ext_attr20 ,
phbd.uom_ext_attr1 = phb.uom_ext_attr1 ,
phbd.uom_ext_attr2 = phb.uom_ext_attr2 ,
phbd.uom_ext_attr3 = phb.uom_ext_attr3 ,
phbd.uom_ext_attr4= phb.uom_ext_attr4,
phbd.uom_ext_attr5 = phb.uom_ext_attr5 ,
phbd.uom_ext_attr6 = phb.uom_ext_attr6 ,
phbd.uom_ext_attr7 = phb.uom_ext_attr7 ,
phbd.uom_ext_attr8 = phb.uom_ext_attr8 ,
phbd.uom_ext_attr9 = phb.uom_ext_attr9 ,
phbd.uom_ext_attr10 = phb.uom_ext_attr10 ,
phbd.uom_ext_attr11 = phb.uom_ext_attr11 ,
phbd.uom_ext_attr12 = phb.uom_ext_attr12 ,
phbd.uom_ext_attr13 = phb.uom_ext_attr13 ,
phbd.uom_ext_attr14 = phb.uom_ext_attr14 ,
phbd.uom_ext_attr15 = phb.uom_ext_attr15 ,
phbd.uom_ext_attr16 = phb.uom_ext_attr16 ,
phbd.uom_ext_attr17 = phb.uom_ext_attr17 ,
phbd.uom_ext_attr18 = phb.uom_ext_attr18 ,
phbd.uom_ext_attr19 = phb.uom_ext_attr19 ,
phbd.uom_ext_attr20 = phb.uom_ext_attr20 ,
phbd.d_ext_attr1 = phb.d_ext_attr1 ,
phbd.d_ext_attr2 = phb.d_ext_attr2 ,
phbd.d_ext_attr3 = phb.d_ext_attr3 ,
phbd.d_ext_attr4 = phb.d_ext_attr4 ,
phbd.d_ext_attr5 = phb.d_ext_attr5 ,
phbd.d_ext_attr6 = phb.d_ext_attr6 ,
phbd.d_ext_attr7 = phb.d_ext_attr7 ,
phbd.d_ext_attr8 = phb.d_ext_attr8 ,
phbd.d_ext_attr9 = phb.d_ext_attr9 ,
phbd.d_ext_attr10 = phb.d_ext_attr10 ,
phbd.revision_num = phb.revision_num
WHEN NOT MATCHED THEN
INSERT
(phbd.extension_id ,
phbd.attr_group_id ,
phbd.po_header_id ,
phbd.data_level_id ,
phbd.pk1_value ,
phbd.pk2_value ,
phbd.pk3_value ,
phbd.pk4_value ,
phbd.pk5_value ,
phbd.last_update_date ,
phbd.last_updated_by ,
phbd.last_update_login ,
phbd.created_by ,
phbd.creation_date ,
phbd.c_ext_attr1 ,
phbd.c_ext_attr2 ,
phbd.c_ext_attr3 ,
phbd.c_ext_attr4 ,
phbd.c_ext_attr5 ,
phbd.c_ext_attr6 ,
phbd.c_ext_attr7 ,
phbd.c_ext_attr8 ,
phbd.c_ext_attr9 ,
phbd.c_ext_attr10 ,
phbd.c_ext_attr11 ,
phbd.c_ext_attr12 ,
phbd.c_ext_attr13 ,
phbd.c_ext_attr14 ,
phbd.c_ext_attr15 ,
phbd.c_ext_attr16 ,
phbd.c_ext_attr17 ,
phbd.c_ext_attr18 ,
phbd.c_ext_attr19 ,
phbd.c_ext_attr20 ,
phbd.c_ext_attr21 ,
phbd.c_ext_attr22 ,
phbd.c_ext_attr23 ,
phbd.c_ext_attr24 ,
phbd.c_ext_attr25 ,
phbd.c_ext_attr26 ,
phbd.c_ext_attr27 ,
phbd.c_ext_attr28 ,
phbd.c_ext_attr29 ,
phbd.c_ext_attr30 ,
phbd.c_ext_attr31 ,
phbd.c_ext_attr32 ,
phbd.c_ext_attr33 ,
phbd.c_ext_attr34 ,
phbd.c_ext_attr35 ,
phbd.c_ext_attr36 ,
phbd.c_ext_attr37 ,
phbd.c_ext_attr38 ,
phbd.c_ext_attr39 ,
phbd.c_ext_attr40 ,
phbd.n_ext_attr1 ,
phbd.n_ext_attr2 ,
phbd.n_ext_attr3 ,
phbd.n_ext_attr4 ,
phbd.n_ext_attr5 ,
phbd.n_ext_attr6 ,
phbd.n_ext_attr7 ,
phbd.n_ext_attr8 ,
phbd.n_ext_attr9 ,
phbd.n_ext_attr10 ,
phbd.n_ext_attr11 ,
phbd.n_ext_attr12 ,
phbd.n_ext_attr13 ,
phbd.n_ext_attr14 ,
phbd.n_ext_attr15 ,
phbd.n_ext_attr16 ,
phbd.n_ext_attr17 ,
phbd.n_ext_attr18 ,
phbd.n_ext_attr19 ,
phbd.n_ext_attr20 ,
phbd.uom_ext_attr1 ,
phbd.uom_ext_attr2 ,
phbd.uom_ext_attr3 ,
phbd.uom_ext_attr4 ,
phbd.uom_ext_attr5 ,
phbd.uom_ext_attr6 ,
phbd.uom_ext_attr7 ,
phbd.uom_ext_attr8 ,
phbd.uom_ext_attr9 ,
phbd.uom_ext_attr10 ,
phbd.uom_ext_attr11 ,
phbd.uom_ext_attr12 ,
phbd.uom_ext_attr13 ,
phbd.uom_ext_attr14 ,
phbd.uom_ext_attr15 ,
phbd.uom_ext_attr16 ,
phbd.uom_ext_attr17 ,
phbd.uom_ext_attr18 ,
phbd.uom_ext_attr19 ,
phbd.uom_ext_attr20 ,
phbd.d_ext_attr1 ,
phbd.d_ext_attr2 ,
phbd.d_ext_attr3 ,
phbd.d_ext_attr4 ,
phbd.d_ext_attr5 ,
phbd.d_ext_attr6 ,
phbd.d_ext_attr7 ,
phbd.d_ext_attr8 ,
phbd.d_ext_attr9 ,
phbd.d_ext_attr10 ,
phbd.uda_template_id ,
phbd.draft_id ,
phbd.revision_num )
VALUES
(EGO_EXTFWK_S.NEXTVAL ,
phb.attr_group_id ,
phb.po_header_id ,
phb.data_level_id ,
phb.pk1_value ,
phb.pk2_value ,
phb.pk3_value ,
phb.pk4_value ,
phb.pk5_value ,
phb.last_update_date ,
phb.last_updated_by ,
phb.last_update_login ,
phb.created_by ,
phb.creation_date ,
phb.c_ext_attr1 ,
phb.c_ext_attr2 ,
phb.c_ext_attr3 ,
phb.c_ext_attr4 ,
phb.c_ext_attr5 ,
phb.c_ext_attr6 ,
phb.c_ext_attr7 ,
phb.c_ext_attr8 ,
phb.c_ext_attr9 ,
phb.c_ext_attr10 ,
phb.c_ext_attr11 ,
phb.c_ext_attr12 ,
phb.c_ext_attr13 ,
phb.c_ext_attr14 ,
phb.c_ext_attr15 ,
phb.c_ext_attr16 ,
phb.c_ext_attr17 ,
phb.c_ext_attr18 ,
phb.c_ext_attr19 ,
phb.c_ext_attr20 ,
phb.c_ext_attr21 ,
phb.c_ext_attr22 ,
phb.c_ext_attr23 ,
phb.c_ext_attr24 ,
phb.c_ext_attr25 ,
phb.c_ext_attr26 ,
phb.c_ext_attr27 ,
phb.c_ext_attr28 ,
phb.c_ext_attr29 ,
phb.c_ext_attr30 ,
phb.c_ext_attr31 ,
phb.c_ext_attr32 ,
phb.c_ext_attr33 ,
phb.c_ext_attr34 ,
phb.c_ext_attr35 ,
phb.c_ext_attr36 ,
phb.c_ext_attr37 ,
phb.c_ext_attr38 ,
phb.c_ext_attr39 ,
phb.c_ext_attr40 ,
phb.n_ext_attr1 ,
phb.n_ext_attr2 ,
phb.n_ext_attr3 ,
phb.n_ext_attr4 ,
phb.n_ext_attr5 ,
phb.n_ext_attr6 ,
phb.n_ext_attr7 ,
phb.n_ext_attr8 ,
phb.n_ext_attr9 ,
phb.n_ext_attr10 ,
phb.n_ext_attr11 ,
phb.n_ext_attr12 ,
phb.n_ext_attr13 ,
phb.n_ext_attr14 ,
phb.n_ext_attr15 ,
phb.n_ext_attr16 ,
phb.n_ext_attr17 ,
phb.n_ext_attr18 ,
phb.n_ext_attr19 ,
phb.n_ext_attr20 ,
phb.uom_ext_attr1 ,
phb.uom_ext_attr2 ,
phb.uom_ext_attr3 ,
phb.uom_ext_attr4 ,
phb.uom_ext_attr5 ,
phb.uom_ext_attr6 ,
phb.uom_ext_attr7 ,
phb.uom_ext_attr8 ,
phb.uom_ext_attr9 ,
phb.uom_ext_attr10 ,
phb.uom_ext_attr11 ,
phb.uom_ext_attr12 ,
phb.uom_ext_attr13 ,
phb.uom_ext_attr14 ,
phb.uom_ext_attr15 ,
phb.uom_ext_attr16 ,
phb.uom_ext_attr17 ,
phb.uom_ext_attr18 ,
phb.uom_ext_attr19 ,
phb.uom_ext_attr20 ,
phb.d_ext_attr1 ,
phb.d_ext_attr2 ,
phb.d_ext_attr3 ,
phb.d_ext_attr4 ,
phb.d_ext_attr5 ,
phb.d_ext_attr6 ,
phb.d_ext_attr7 ,
phb.d_ext_attr8 ,
phb.d_ext_attr9 ,
phb.d_ext_attr10 ,
phb.uda_template_id ,
p_draft_id ,
phb.revision_num );
PO_LOG.stmt(d_module,d_position ,'Updated po_headers_all_ext_b for Single Row attr grp and c_ext_attr40 not in PO_UDA_ADDRESS_TYPES');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
DELETE FROM po_headers_all_ext_b pheb
WHERE pheb.po_header_id=l_po_header_id
AND pheb.draft_id = p_draft_id
AND (pheb.c_ext_attr40 NOT IN ('PO_UDA_ADDRESS_TYPES', 'PO_MOD_UDA_ADDRESS_TYPES')
OR pheb.c_ext_attr40 IS NULL)
AND NOT EXISTS (SELECT 'only S or Null Locks'
FROM po_entity_locks pel
WHERE pel.lock_type IN ('P','F')
AND pel.entity_pk1= pheb.po_header_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY
AND pel.lock_by_draft_id = p_draft_id )
AND EXISTS (SELECT 'if multi row attr grp'
FROM ego_fnd_dsc_flx_ctx_ext ag
WHERE pheb.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'Y');
PO_LOG.stmt(d_module,d_position ,'Deleted from po_headers_all_ext_b for Multi Row attr grp and c_ext_attr40 not in PO_UDA_ADDRESS_TYPES');
INSERT INTO po_headers_all_ext_b phbd
( phbd.extension_id ,
phbd.attr_group_id ,
phbd.po_header_id ,
phbd.data_level_id ,
phbd.pk1_value ,
phbd.pk2_value ,
phbd.pk3_value ,
phbd.pk4_value ,
phbd.pk5_value ,
phbd.last_update_date,
phbd.last_updated_by ,
phbd.last_update_login ,
phbd.created_by ,
phbd.creation_date,
phbd.c_ext_attr1,
phbd.c_ext_attr2 ,
phbd.c_ext_attr3 ,
phbd.c_ext_attr4 ,
phbd.c_ext_attr5 ,
phbd.c_ext_attr6 ,
phbd.c_ext_attr7 ,
phbd.c_ext_attr8 ,
phbd.c_ext_attr9 ,
phbd.c_ext_attr10 ,
phbd.c_ext_attr11 ,
phbd.c_ext_attr12 ,
phbd.c_ext_attr13 ,
phbd.c_ext_attr14 ,
phbd.c_ext_attr15 ,
phbd.c_ext_attr16 ,
phbd.c_ext_attr17 ,
phbd.c_ext_attr18 ,
phbd.c_ext_attr19 ,
phbd.c_ext_attr20 ,
phbd.c_ext_attr21 ,
phbd.c_ext_attr22 ,
phbd.c_ext_attr23 ,
phbd.c_ext_attr24 ,
phbd.c_ext_attr25 ,
phbd.c_ext_attr26 ,
phbd.c_ext_attr27 ,
phbd.c_ext_attr28 ,
phbd.c_ext_attr29 ,
phbd.c_ext_attr30 ,
phbd.c_ext_attr31 ,
phbd.c_ext_attr32 ,
phbd.c_ext_attr33 ,
phbd.c_ext_attr34 ,
phbd.c_ext_attr35 ,
phbd.c_ext_attr36 ,
phbd.c_ext_attr37 ,
phbd.c_ext_attr38 ,
phbd.c_ext_attr39 ,
phbd.c_ext_attr40 ,
phbd.n_ext_attr1 ,
phbd.n_ext_attr2 ,
phbd.n_ext_attr3 ,
phbd.n_ext_attr4 ,
phbd.n_ext_attr5 ,
phbd.n_ext_attr6 ,
phbd.n_ext_attr7 ,
phbd.n_ext_attr8 ,
phbd.n_ext_attr9 ,
phbd.n_ext_attr10 ,
phbd.n_ext_attr11 ,
phbd.n_ext_attr12 ,
phbd.n_ext_attr13 ,
phbd.n_ext_attr14 ,
phbd.n_ext_attr15 ,
phbd.n_ext_attr16 ,
phbd.n_ext_attr17 ,
phbd.n_ext_attr18 ,
phbd.n_ext_attr19 ,
phbd.n_ext_attr20 ,
phbd.uom_ext_attr1 ,
phbd.uom_ext_attr2 ,
phbd.uom_ext_attr3 ,
phbd.uom_ext_attr4,
phbd.uom_ext_attr5 ,
phbd.uom_ext_attr6 ,
phbd.uom_ext_attr7 ,
phbd.uom_ext_attr8 ,
phbd.uom_ext_attr9 ,
phbd.uom_ext_attr10 ,
phbd.uom_ext_attr11 ,
phbd.uom_ext_attr12 ,
phbd.uom_ext_attr13 ,
phbd.uom_ext_attr14 ,
phbd.uom_ext_attr15 ,
phbd.uom_ext_attr16 ,
phbd.uom_ext_attr17 ,
phbd.uom_ext_attr18 ,
phbd.uom_ext_attr19 ,
phbd.uom_ext_attr20 ,
phbd.d_ext_attr1 ,
phbd.d_ext_attr2 ,
phbd.d_ext_attr3 ,
phbd.d_ext_attr4 ,
phbd.d_ext_attr5 ,
phbd.d_ext_attr6 ,
phbd.d_ext_attr7 ,
phbd.d_ext_attr8 ,
phbd.d_ext_attr9 ,
phbd.d_ext_attr10,
phbd.uda_template_id ,
phbd.draft_id ,
phbd.revision_num )
SELECT
EGO_EXTFWK_S.NEXTVAL ,
phb.attr_group_id ,
phb.po_header_id ,
phb.data_level_id ,
phb.pk1_value ,
phb.pk2_value ,
phb.pk3_value ,
phb.pk4_value ,
phb.pk5_value ,
phb.last_update_date,
phb.last_updated_by ,
phb.last_update_login ,
phb.created_by ,
phb.creation_date,
phb.c_ext_attr1,
phb.c_ext_attr2 ,
phb.c_ext_attr3 ,
phb.c_ext_attr4 ,
phb.c_ext_attr5 ,
phb.c_ext_attr6 ,
phb.c_ext_attr7 ,
phb.c_ext_attr8 ,
phb.c_ext_attr9 ,
phb.c_ext_attr10 ,
phb.c_ext_attr11 ,
phb.c_ext_attr12 ,
phb.c_ext_attr13 ,
phb.c_ext_attr14 ,
phb.c_ext_attr15 ,
phb.c_ext_attr16 ,
phb.c_ext_attr17 ,
phb.c_ext_attr18 ,
phb.c_ext_attr19 ,
phb.c_ext_attr20 ,
phb.c_ext_attr21 ,
phb.c_ext_attr22 ,
phb.c_ext_attr23 ,
phb.c_ext_attr24 ,
phb.c_ext_attr25 ,
phb.c_ext_attr26 ,
phb.c_ext_attr27 ,
phb.c_ext_attr28 ,
phb.c_ext_attr29 ,
phb.c_ext_attr30 ,
phb.c_ext_attr31 ,
phb.c_ext_attr32 ,
phb.c_ext_attr33 ,
phb.c_ext_attr34 ,
phb.c_ext_attr35 ,
phb.c_ext_attr36 ,
phb.c_ext_attr37 ,
phb.c_ext_attr38 ,
phb.c_ext_attr39 ,
phb.c_ext_attr40 ,
phb.n_ext_attr1 ,
phb.n_ext_attr2 ,
phb.n_ext_attr3 ,
phb.n_ext_attr4 ,
phb.n_ext_attr5 ,
phb.n_ext_attr6 ,
phb.n_ext_attr7 ,
phb.n_ext_attr8 ,
phb.n_ext_attr9 ,
phb.n_ext_attr10 ,
phb.n_ext_attr11 ,
phb.n_ext_attr12 ,
phb.n_ext_attr13 ,
phb.n_ext_attr14 ,
phb.n_ext_attr15 ,
phb.n_ext_attr16 ,
phb.n_ext_attr17 ,
phb.n_ext_attr18 ,
phb.n_ext_attr19 ,
phb.n_ext_attr20 ,
phb.uom_ext_attr1 ,
phb.uom_ext_attr2 ,
phb.uom_ext_attr3 ,
phb.uom_ext_attr4,
phb.uom_ext_attr5 ,
phb.uom_ext_attr6 ,
phb.uom_ext_attr7 ,
phb.uom_ext_attr8 ,
phb.uom_ext_attr9 ,
phb.uom_ext_attr10 ,
phb.uom_ext_attr11 ,
phb.uom_ext_attr12 ,
phb.uom_ext_attr13 ,
phb.uom_ext_attr14 ,
phb.uom_ext_attr15 ,
phb.uom_ext_attr16 ,
phb.uom_ext_attr17 ,
phb.uom_ext_attr18 ,
phb.uom_ext_attr19 ,
phb.uom_ext_attr20 ,
phb.d_ext_attr1 ,
phb.d_ext_attr2 ,
phb.d_ext_attr3 ,
phb.d_ext_attr4 ,
phb.d_ext_attr5 ,
phb.d_ext_attr6 ,
phb.d_ext_attr7 ,
phb.d_ext_attr8 ,
phb.d_ext_attr9 ,
phb.d_ext_attr10,
phb.uda_template_id ,
p_draft_id ,
phb.revision_num
FROM po_headers_all_ext_b phb --base
WHERE phb.draft_id = -1
AND phb.po_header_id = l_po_header_id
AND (phb.c_ext_attr40 NOT IN ('PO_UDA_ADDRESS_TYPES', 'PO_MOD_UDA_ADDRESS_TYPES')
OR phb.c_ext_attr40 IS NULL)
AND NOT EXISTS (SELECT 'only S or Null Locks'
FROM po_entity_locks pel
WHERE pel.lock_type IN ('P','F')
AND pel.entity_pk1= phb.po_header_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY
AND pel.lock_by_draft_id = p_draft_id )
AND EXISTS (SELECT 'Multi row attr grp'
FROM ego_fnd_dsc_flx_ctx_ext ag
WHERE phb.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'Y');
PO_LOG.stmt(d_module,d_position ,'Inserted Into po_headers_all_ext_b for Multi Row attr grp from base tables');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
SELECT b.extension_id
BULK COLLECT
INTO l_entension_id_tbl
FROM po_headers_all_ext_b b
WHERE draft_id = p_draft_id
AND po_header_id = l_po_header_id
AND NOT EXISTS (SELECT 'tl table record exists'
FROM po_headers_all_ext_tl tl
WHERE b.extension_id = tl.extension_id);
SELECT language_code
INTO l_base_lang
FROM fnd_languages
WHERE installed_flag ='B';
DELETE FROM po_headers_all_ext_tl phaet
WHERE phaet.po_header_id=l_po_header_id
AND phaet.draft_id = p_draft_id
AND EXISTS (SELECT 'if multi row attr grp'
FROM ego_fnd_dsc_flx_ctx_ext ag
WHERE phaet.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'Y')
AND NOT EXISTS (SELECT 'only S or Null Locks'
FROM po_entity_locks pel
WHERE pel.lock_type IN ('P','F')
AND pel.entity_pk1= phaet.po_header_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY
AND pel.lock_by_draft_id = p_draft_id )
AND NOT EXISTS (SELECT 'Exclude Uda attributes'
FROM po_headers_all_ext_b phb
WHERE PHB.EXTENSION_ID = phaet.EXTENSION_ID
AND phb.c_ext_attr40 = 'PO_UDA_ADDRESS_TYPES'
);
INSERT INTO po_headers_all_ext_tl tl
( tl.extension_id,
tl.attr_group_id,
tl.po_header_id,
tl.source_lang,
tl.language,
tl.data_level_id,
tl.pk1_value,
tl.pk2_value,
tl.pk3_value,
tl.pk4_value,
tl.pk5_value,
tl.last_update_date,
tl.last_updated_by ,
tl.last_update_login,
tl.created_by,
tl.creation_date ,
tl.uda_template_id,
tl.draft_id
)
SELECT l_entension_id_tbl(i),
b.attr_group_id,
b.po_header_id,
l_base_lang,
l.language_code,
b.data_level_id,
b.pk1_value,
b.pk2_value,
b.pk3_value,
b.pk4_value,
b.pk5_value,
b.last_update_date,
b.last_updated_by ,
b.last_update_login,
b.created_by,
b.creation_date ,
b.uda_template_id,
p_draft_id
FROM po_headers_all_ext_b b,
fnd_languages l
WHERE b.extension_id = l_entension_id_tbl(i)
AND l.installed_flag IN ('B', 'I')
;
PO_LOG.stmt(d_module,d_position ,'Updated po_headers_all_ext_tl for S or No locks');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
UPDATE po_lines_all_ext_b plbd
SET ( plbd.c_ext_attr1,
plbd.c_ext_attr2 ,
plbd.c_ext_attr3 ,
plbd.c_ext_attr4 ,
plbd.c_ext_attr5 ,
plbd.c_ext_attr6 ,
plbd.c_ext_attr7 ,
plbd.c_ext_attr8 ,
plbd.c_ext_attr9 ,
plbd.c_ext_attr10 ,
plbd.c_ext_attr11 ,
plbd.c_ext_attr12 ,
plbd.c_ext_attr13 ,
plbd.c_ext_attr14 ,
plbd.c_ext_attr15 ,
plbd.c_ext_attr16 ,
plbd.c_ext_attr17 ,
plbd.c_ext_attr18 ,
plbd.c_ext_attr19 ,
plbd.c_ext_attr20 ,
plbd.c_ext_attr21 ,
plbd.c_ext_attr22 ,
plbd.c_ext_attr23 ,
plbd.c_ext_attr24 ,
plbd.c_ext_attr25 ,
plbd.c_ext_attr26 ,
plbd.c_ext_attr27 ,
plbd.c_ext_attr28 ,
plbd.c_ext_attr29 ,
plbd.c_ext_attr30 ,
plbd.c_ext_attr31 ,
plbd.c_ext_attr32 ,
plbd.c_ext_attr33 ,
plbd.c_ext_attr34 ,
plbd.c_ext_attr35 ,
plbd.c_ext_attr36 ,
plbd.c_ext_attr37 ,
plbd.c_ext_attr38 ,
plbd.c_ext_attr39 ,
plbd.c_ext_attr40 ,
plbd.n_ext_attr1 ,
plbd.n_ext_attr2 ,
plbd.n_ext_attr3 ,
plbd.n_ext_attr4 ,
plbd.n_ext_attr5 ,
plbd.n_ext_attr6 ,
plbd.n_ext_attr7 ,
plbd.n_ext_attr8 ,
plbd.n_ext_attr9 ,
plbd.n_ext_attr10 ,
plbd.n_ext_attr11 ,
plbd.n_ext_attr12 ,
plbd.n_ext_attr13 ,
plbd.n_ext_attr14 ,
plbd.n_ext_attr15 ,
plbd.n_ext_attr16 ,
plbd.n_ext_attr17 ,
plbd.n_ext_attr18 ,
plbd.n_ext_attr19 ,
plbd.n_ext_attr20 ,
plbd.uom_ext_attr1 ,
plbd.uom_ext_attr2 ,
plbd.uom_ext_attr3 ,
plbd.uom_ext_attr4,
plbd.uom_ext_attr5 ,
plbd.uom_ext_attr6 ,
plbd.uom_ext_attr7 ,
plbd.uom_ext_attr8 ,
plbd.uom_ext_attr9 ,
plbd.uom_ext_attr10 ,
plbd.uom_ext_attr11 ,
plbd.uom_ext_attr12 ,
plbd.uom_ext_attr13 ,
plbd.uom_ext_attr14 ,
plbd.uom_ext_attr15 ,
plbd.uom_ext_attr16 ,
plbd.uom_ext_attr17 ,
plbd.uom_ext_attr18 ,
plbd.uom_ext_attr19 ,
plbd.uom_ext_attr20 ,
plbd.d_ext_attr1 ,
plbd.d_ext_attr2 ,
plbd.d_ext_attr3 ,
plbd.d_ext_attr4 ,
plbd.d_ext_attr5 ,
plbd.d_ext_attr6 ,
plbd.d_ext_attr7 ,
plbd.d_ext_attr8 ,
plbd.d_ext_attr9 ,
plbd.d_ext_attr10
)
= (SELECT
plb.c_ext_attr1,
plb.c_ext_attr2 ,
plb.c_ext_attr3 ,
plb.c_ext_attr4 ,
plb.c_ext_attr5 ,
plb.c_ext_attr6 ,
plb.c_ext_attr7 ,
plb.c_ext_attr8 ,
plb.c_ext_attr9 ,
plb.c_ext_attr10 ,
plb.c_ext_attr11 ,
plb.c_ext_attr12 ,
plb.c_ext_attr13 ,
plb.c_ext_attr14 ,
plb.c_ext_attr15 ,
plb.c_ext_attr16 ,
plb.c_ext_attr17 ,
plb.c_ext_attr18 ,
plb.c_ext_attr19 ,
plb.c_ext_attr20 ,
plb.c_ext_attr21 ,
plb.c_ext_attr22 ,
plb.c_ext_attr23 ,
plb.c_ext_attr24 ,
plb.c_ext_attr25 ,
plb.c_ext_attr26 ,
plb.c_ext_attr27 ,
plb.c_ext_attr28 ,
plb.c_ext_attr29 ,
plb.c_ext_attr30 ,
plb.c_ext_attr31 ,
plb.c_ext_attr32 ,
plb.c_ext_attr33 ,
plb.c_ext_attr34 ,
plb.c_ext_attr35 ,
plb.c_ext_attr36 ,
plb.c_ext_attr37 ,
plb.c_ext_attr38 ,
plb.c_ext_attr39 ,
plb.c_ext_attr40 ,
plb.n_ext_attr1 ,
plb.n_ext_attr2 ,
plb.n_ext_attr3 ,
plb.n_ext_attr4 ,
plb.n_ext_attr5 ,
plb.n_ext_attr6 ,
plb.n_ext_attr7 ,
plb.n_ext_attr8 ,
plb.n_ext_attr9 ,
plb.n_ext_attr10 ,
plb.n_ext_attr11 ,
plb.n_ext_attr12 ,
plb.n_ext_attr13 ,
plb.n_ext_attr14 ,
plb.n_ext_attr15 ,
plb.n_ext_attr16 ,
plb.n_ext_attr17 ,
plb.n_ext_attr18 ,
plb.n_ext_attr19 ,
plb.n_ext_attr20 ,
plb.uom_ext_attr1 ,
plb.uom_ext_attr2 ,
plb.uom_ext_attr3 ,
plb.uom_ext_attr4 ,
plb.uom_ext_attr5 ,
plb.uom_ext_attr6 ,
plb.uom_ext_attr7 ,
plb.uom_ext_attr8 ,
plb.uom_ext_attr9 ,
plb.uom_ext_attr10 ,
plb.uom_ext_attr11 ,
plb.uom_ext_attr12 ,
plb.uom_ext_attr13 ,
plb.uom_ext_attr14 ,
plb.uom_ext_attr15 ,
plb.uom_ext_attr16 ,
plb.uom_ext_attr17 ,
plb.uom_ext_attr18 ,
plb.uom_ext_attr19 ,
plb.uom_ext_attr20 ,
plb.d_ext_attr1 ,
plb.d_ext_attr2 ,
plb.d_ext_attr3 ,
plb.d_ext_attr4 ,
plb.d_ext_attr5 ,
plb.d_ext_attr6 ,
plb.d_ext_attr7 ,
plb.d_ext_attr8 ,
plb.d_ext_attr9 ,
plb.d_ext_attr10
FROM po_lines_all_ext_b plb --base
WHERE plb.po_line_id = p_po_line_s_no_lck_tbl(i)
AND plb.draft_id = -1
AND plb.attr_group_id = plbd.attr_group_id
)
WHERE plbd.po_line_id = p_po_line_s_no_lck_tbl(i)
AND plbd.draft_id = p_draft_id
AND NOT EXISTS (SELECT 'exclude PRICING attribute category'
FROM po_uda_ag_template_usages puatu,
po_lines_all pol
WHERE puatu.template_id = pol.uda_template_id
AND plbd.po_line_id = pol.po_line_id
AND puatu.attribute_category = 'PRICING'
AND puatu.attribute_group_id = plbd.attr_group_id
);
PO_LOG.stmt(d_module,d_position ,'Updated po_lines_all_ext_b for S and No locks');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
UPDATE po_lines_all_ext_b plbd
SET ( plbd.c_ext_attr1,
plbd.c_ext_attr2 ,
plbd.c_ext_attr3 ,
plbd.c_ext_attr4 ,
plbd.c_ext_attr5 ,
plbd.c_ext_attr6 ,
plbd.c_ext_attr7 ,
plbd.c_ext_attr8 ,
plbd.c_ext_attr9 ,
plbd.c_ext_attr10 ,
plbd.c_ext_attr11 ,
plbd.c_ext_attr12 ,
plbd.c_ext_attr13 ,
plbd.c_ext_attr14 ,
plbd.c_ext_attr15 ,
plbd.c_ext_attr16 ,
plbd.c_ext_attr17 ,
plbd.c_ext_attr18 ,
plbd.c_ext_attr19 ,
plbd.c_ext_attr20 ,
plbd.c_ext_attr21 ,
plbd.c_ext_attr22 ,
plbd.c_ext_attr23 ,
plbd.c_ext_attr24 ,
plbd.c_ext_attr25 ,
plbd.c_ext_attr26 ,
plbd.c_ext_attr27 ,
plbd.c_ext_attr28 ,
plbd.c_ext_attr29 ,
plbd.c_ext_attr30 ,
plbd.c_ext_attr31 ,
plbd.c_ext_attr32 ,
plbd.c_ext_attr33 ,
plbd.c_ext_attr34 ,
plbd.c_ext_attr35 ,
plbd.c_ext_attr36 ,
plbd.c_ext_attr37 ,
plbd.c_ext_attr38 ,
plbd.c_ext_attr39 ,
plbd.c_ext_attr40 ,
plbd.n_ext_attr1 ,
plbd.n_ext_attr2 ,
plbd.n_ext_attr3 ,
plbd.n_ext_attr4 ,
plbd.n_ext_attr5 ,
plbd.n_ext_attr6 ,
plbd.n_ext_attr7 ,
plbd.n_ext_attr8 ,
plbd.n_ext_attr9 ,
plbd.n_ext_attr10 ,
plbd.n_ext_attr11 ,
plbd.n_ext_attr12 ,
plbd.n_ext_attr13 ,
plbd.n_ext_attr14 ,
plbd.n_ext_attr15 ,
plbd.n_ext_attr16 ,
plbd.n_ext_attr17 ,
plbd.n_ext_attr18 ,
plbd.n_ext_attr19 ,
plbd.n_ext_attr20 ,
plbd.uom_ext_attr1 ,
plbd.uom_ext_attr2 ,
plbd.uom_ext_attr3 ,
plbd.uom_ext_attr4,
plbd.uom_ext_attr5 ,
plbd.uom_ext_attr6 ,
plbd.uom_ext_attr7 ,
plbd.uom_ext_attr8 ,
plbd.uom_ext_attr9 ,
plbd.uom_ext_attr10 ,
plbd.uom_ext_attr11 ,
plbd.uom_ext_attr12 ,
plbd.uom_ext_attr13 ,
plbd.uom_ext_attr14 ,
plbd.uom_ext_attr15 ,
plbd.uom_ext_attr16 ,
plbd.uom_ext_attr17 ,
plbd.uom_ext_attr18 ,
plbd.uom_ext_attr19 ,
plbd.uom_ext_attr20 ,
plbd.d_ext_attr1 ,
plbd.d_ext_attr2 ,
plbd.d_ext_attr3 ,
plbd.d_ext_attr4 ,
plbd.d_ext_attr5 ,
plbd.d_ext_attr6 ,
plbd.d_ext_attr7 ,
plbd.d_ext_attr8 ,
plbd.d_ext_attr9 ,
plbd.d_ext_attr10 )
= (SELECT
plb.c_ext_attr1,
plb.c_ext_attr2 ,
plb.c_ext_attr3 ,
plb.c_ext_attr4 ,
plb.c_ext_attr5 ,
plb.c_ext_attr6 ,
plb.c_ext_attr7 ,
plb.c_ext_attr8 ,
plb.c_ext_attr9 ,
plb.c_ext_attr10 ,
plb.c_ext_attr11 ,
plb.c_ext_attr12 ,
plb.c_ext_attr13 ,
plb.c_ext_attr14 ,
plb.c_ext_attr15 ,
plb.c_ext_attr16 ,
plb.c_ext_attr17 ,
plb.c_ext_attr18 ,
plb.c_ext_attr19 ,
plb.c_ext_attr20 ,
plb.c_ext_attr21 ,
plb.c_ext_attr22 ,
plb.c_ext_attr23 ,
plb.c_ext_attr24 ,
plb.c_ext_attr25 ,
plb.c_ext_attr26 ,
plb.c_ext_attr27 ,
plb.c_ext_attr28 ,
plb.c_ext_attr29 ,
plb.c_ext_attr30 ,
plb.c_ext_attr31 ,
plb.c_ext_attr32 ,
plb.c_ext_attr33 ,
plb.c_ext_attr34 ,
plb.c_ext_attr35 ,
plb.c_ext_attr36 ,
plb.c_ext_attr37 ,
plb.c_ext_attr38 ,
plb.c_ext_attr39 ,
plb.c_ext_attr40 ,
plb.n_ext_attr1 ,
plb.n_ext_attr2 ,
plb.n_ext_attr3 ,
plb.n_ext_attr4 ,
plb.n_ext_attr5 ,
plb.n_ext_attr6 ,
plb.n_ext_attr7 ,
plb.n_ext_attr8 ,
plb.n_ext_attr9 ,
plb.n_ext_attr10 ,
plb.n_ext_attr11 ,
plb.n_ext_attr12 ,
plb.n_ext_attr13 ,
plb.n_ext_attr14 ,
plb.n_ext_attr15 ,
plb.n_ext_attr16 ,
plb.n_ext_attr17 ,
plb.n_ext_attr18 ,
plb.n_ext_attr19 ,
plb.n_ext_attr20 ,
plb.uom_ext_attr1 ,
plb.uom_ext_attr2 ,
plb.uom_ext_attr3 ,
plb.uom_ext_attr4 ,
plb.uom_ext_attr5 ,
plb.uom_ext_attr6 ,
plb.uom_ext_attr7 ,
plb.uom_ext_attr8 ,
plb.uom_ext_attr9 ,
plb.uom_ext_attr10 ,
plb.uom_ext_attr11 ,
plb.uom_ext_attr12 ,
plb.uom_ext_attr13 ,
plb.uom_ext_attr14 ,
plb.uom_ext_attr15 ,
plb.uom_ext_attr16 ,
plb.uom_ext_attr17 ,
plb.uom_ext_attr18 ,
plb.uom_ext_attr19 ,
plb.uom_ext_attr20 ,
plb.d_ext_attr1 ,
plb.d_ext_attr2 ,
plb.d_ext_attr3 ,
plb.d_ext_attr4 ,
plb.d_ext_attr5 ,
plb.d_ext_attr6 ,
plb.d_ext_attr7 ,
plb.d_ext_attr8 ,
plb.d_ext_attr9 ,
plb.d_ext_attr10
FROM po_lines_all_ext_b plb --base
WHERE plb.po_line_id = plbd.po_line_id
AND plb.draft_id = -1
AND plb.attr_group_id = plbd.attr_group_id
AND plb.pk1_value IS NULL
)
WHERE plbd.po_line_id = p_po_line_s_no_p_lck_tbl(i)
AND plbd.draft_id = -1
AND plbd.pk1_value = p_draft_id
AND EXISTS (SELECT 'include only PRICING attribute category'
FROM po_uda_ag_template_usages puatu,
po_lines_all pol
WHERE puatu.template_id = pol.uda_template_id
AND plbd.po_line_id = pol.po_line_id
AND puatu.attribute_category = 'PRICING'
AND puatu.attribute_group_id = plbd.attr_group_id
);
PO_LOG.stmt(d_module,d_position ,'updated po_lines_all_ext_b corresponding to Old MOdification');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
UPDATE po_lines_all_ext_b plbd
SET ( plbd.c_ext_attr1,
plbd.c_ext_attr2 ,
plbd.c_ext_attr3 ,
plbd.c_ext_attr4 ,
plbd.c_ext_attr5 ,
plbd.c_ext_attr6 ,
plbd.c_ext_attr7 ,
plbd.c_ext_attr8 ,
plbd.c_ext_attr9 ,
plbd.c_ext_attr10 ,
plbd.c_ext_attr11 ,
plbd.c_ext_attr12 ,
plbd.c_ext_attr13 ,
plbd.c_ext_attr14 ,
plbd.c_ext_attr15 ,
plbd.c_ext_attr16 ,
plbd.c_ext_attr17 ,
plbd.c_ext_attr18 ,
plbd.c_ext_attr19 ,
plbd.c_ext_attr20 ,
plbd.c_ext_attr21 ,
plbd.c_ext_attr22 ,
plbd.c_ext_attr23 ,
plbd.c_ext_attr24 ,
plbd.c_ext_attr25 ,
plbd.c_ext_attr26 ,
plbd.c_ext_attr27 ,
plbd.c_ext_attr28 ,
plbd.c_ext_attr29 ,
plbd.c_ext_attr30 ,
plbd.c_ext_attr31 ,
plbd.c_ext_attr32 ,
plbd.c_ext_attr33 ,
plbd.c_ext_attr34 ,
plbd.c_ext_attr35 ,
plbd.c_ext_attr36 ,
plbd.c_ext_attr37 ,
plbd.c_ext_attr38 ,
plbd.c_ext_attr39 ,
plbd.c_ext_attr40 ,
plbd.uom_ext_attr1 ,
plbd.uom_ext_attr2 ,
plbd.uom_ext_attr3 ,
plbd.uom_ext_attr4,
plbd.uom_ext_attr5 ,
plbd.uom_ext_attr6 ,
plbd.uom_ext_attr7 ,
plbd.uom_ext_attr8 ,
plbd.uom_ext_attr9 ,
plbd.uom_ext_attr10 ,
plbd.uom_ext_attr11 ,
plbd.uom_ext_attr12 ,
plbd.uom_ext_attr13 ,
plbd.uom_ext_attr14 ,
plbd.uom_ext_attr15 ,
plbd.uom_ext_attr16 ,
plbd.uom_ext_attr17 ,
plbd.uom_ext_attr18 ,
plbd.uom_ext_attr19 ,
plbd.uom_ext_attr20 ,
plbd.d_ext_attr1 ,
plbd.d_ext_attr2 ,
plbd.d_ext_attr3 ,
plbd.d_ext_attr4 ,
plbd.d_ext_attr5 ,
plbd.d_ext_attr6 ,
plbd.d_ext_attr7 ,
plbd.d_ext_attr8 ,
plbd.d_ext_attr9 ,
plbd.d_ext_attr10 )
= ( SELECT
plb.c_ext_attr1,
plb.c_ext_attr2 ,
plb.c_ext_attr3 ,
plb.c_ext_attr4 ,
plb.c_ext_attr5 ,
plb.c_ext_attr6 ,
plb.c_ext_attr7 ,
plb.c_ext_attr8 ,
plb.c_ext_attr9 ,
plb.c_ext_attr10 ,
plb.c_ext_attr11 ,
plb.c_ext_attr12 ,
plb.c_ext_attr13 ,
plb.c_ext_attr14 ,
plb.c_ext_attr15 ,
plb.c_ext_attr16 ,
plb.c_ext_attr17 ,
plb.c_ext_attr18 ,
plb.c_ext_attr19 ,
plb.c_ext_attr20 ,
plb.c_ext_attr21 ,
plb.c_ext_attr22 ,
plb.c_ext_attr23 ,
plb.c_ext_attr24 ,
plb.c_ext_attr25 ,
plb.c_ext_attr26 ,
plb.c_ext_attr27 ,
plb.c_ext_attr28 ,
plb.c_ext_attr29 ,
plb.c_ext_attr30 ,
plb.c_ext_attr31 ,
plb.c_ext_attr32 ,
plb.c_ext_attr33 ,
plb.c_ext_attr34 ,
plb.c_ext_attr35 ,
plb.c_ext_attr36 ,
plb.c_ext_attr37 ,
plb.c_ext_attr38 ,
plb.c_ext_attr39 ,
plb.c_ext_attr40 ,
plb.uom_ext_attr1 ,
plb.uom_ext_attr2 ,
plb.uom_ext_attr3 ,
plb.uom_ext_attr4,
plb.uom_ext_attr5 ,
plb.uom_ext_attr6 ,
plb.uom_ext_attr7 ,
plb.uom_ext_attr8 ,
plb.uom_ext_attr9 ,
plb.uom_ext_attr10 ,
plb.uom_ext_attr11 ,
plb.uom_ext_attr12 ,
plb.uom_ext_attr13 ,
plb.uom_ext_attr14 ,
plb.uom_ext_attr15 ,
plb.uom_ext_attr16 ,
plb.uom_ext_attr17 ,
plb.uom_ext_attr18 ,
plb.uom_ext_attr19 ,
plb.uom_ext_attr20 ,
plb.d_ext_attr1 ,
plb.d_ext_attr2 ,
plb.d_ext_attr3 ,
plb.d_ext_attr4 ,
plb.d_ext_attr5 ,
plb.d_ext_attr6 ,
plb.d_ext_attr7 ,
plb.d_ext_attr8 ,
plb.d_ext_attr9 ,
plb.d_ext_attr10
FROM po_lines_all_ext_b plb --base
WHERE plb.po_line_id = plbd.po_line_id
AND plb.draft_id = -1
AND plb.attr_group_id = plbd.attr_group_id
AND plb.pk1_value IS NULL
)
WHERE plbd.po_line_id = p_po_line_s_no_lck_tbl(i)
AND plbd.draft_id = p_draft_id
AND plbd.pk1_value IS NULL
AND EXISTS (SELECT 'include only PRICING attribute category'
FROM po_uda_ag_template_usages puatu,
po_lines_all pol
WHERE puatu.template_id = pol.uda_template_id
AND plbd.po_line_id = pol.po_line_id
AND puatu.attribute_category = 'PRICING'
AND puatu.attribute_group_id = plbd.attr_group_id
);
PO_LOG.stmt(d_module,d_position ,'updated po_lines_all_ext_b corresponding to New MOdification Excluding Numeric attributes');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
UPDATE po_lines_all_ext_b plbd
SET ( plbd.n_ext_attr1 ,
plbd.n_ext_attr2 ,
plbd.n_ext_attr3 ,
plbd.n_ext_attr4 ,
plbd.n_ext_attr5 ,
plbd.n_ext_attr6 ,
plbd.n_ext_attr7 ,
plbd.n_ext_attr8 ,
plbd.n_ext_attr9 ,
plbd.n_ext_attr10 ,
plbd.n_ext_attr11 ,
plbd.n_ext_attr12 ,
plbd.n_ext_attr13 ,
plbd.n_ext_attr14 ,
plbd.n_ext_attr15 ,
plbd.n_ext_attr16 ,
plbd.n_ext_attr17 ,
plbd.n_ext_attr18 ,
plbd.n_ext_attr19 ,
plbd.n_ext_attr20 )
= ( SELECT
plb.n_ext_attr1 + pld.n_ext_attr1 ,
plb.n_ext_attr2 + pld.n_ext_attr2 ,
plb.n_ext_attr3 + pld.n_ext_attr3 ,
plb.n_ext_attr4 + pld.n_ext_attr4 ,
plb.n_ext_attr5 + pld.n_ext_attr5 ,
plb.n_ext_attr6 + pld.n_ext_attr6 ,
plb.n_ext_attr7 + pld.n_ext_attr7 ,
plb.n_ext_attr8 + pld.n_ext_attr8 ,
plb.n_ext_attr9 + pld.n_ext_attr9 ,
plb.n_ext_attr10 + pld.n_ext_attr10 ,
plb.n_ext_attr11 + pld.n_ext_attr11 ,
plb.n_ext_attr12 + pld.n_ext_attr12 ,
plb.n_ext_attr13 + pld.n_ext_attr13 ,
plb.n_ext_attr14 + pld.n_ext_attr14 ,
plb.n_ext_attr15 + pld.n_ext_attr15 ,
plb.n_ext_attr16 + pld.n_ext_attr16 ,
plb.n_ext_attr17 + pld.n_ext_attr17 ,
plb.n_ext_attr18 + pld.n_ext_attr18 ,
plb.n_ext_attr19 + pld.n_ext_attr19 ,
plb.n_ext_attr20 + pld.n_ext_attr20
FROM po_lines_all_ext_b plb,--base
po_lines_all_ext_b pld--delta
WHERE plb.po_line_id = plbd.po_line_id
AND pld.po_line_id = -(plb.po_line_id)
AND plb.attr_group_id = plbd.attr_group_id
AND plb.attr_group_id = pld.attr_group_id
AND plb.draft_id = -1
AND pld.draft_id = -p_draft_id
AND plb.pk1_value IS NULL
AND pld.pk1_value = -p_draft_id
)
WHERE plbd.po_line_id = p_po_line_s_no_p_lck_tbl(i)
AND plbd.draft_id = p_draft_id
AND plbd.pk1_value IS NULL
AND EXISTS (SELECT 'include only PRICING attribute category'
FROM po_uda_ag_template_usages puatu,
po_lines_all pol
WHERE puatu.template_id = pol.uda_template_id
AND plbd.po_line_id = pol.po_line_id
AND puatu.attribute_category = 'PRICING'
AND puatu.attribute_group_id = plbd.attr_group_id
);
PO_LOG.stmt(d_module,d_position ,'updated po_lines_all_ext_b corresponding to New MOdification only for Numeric attributes');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
UPDATE po_lines_all_ext_tl pltd
SET ( pltd.tl_ext_attr1 ,
pltd.tl_ext_attr2 ,
pltd.tl_ext_attr3 ,
pltd.tl_ext_attr4 ,
pltd.tl_ext_attr5 ,
pltd.tl_ext_attr6 ,
pltd.tl_ext_attr7 ,
pltd.tl_ext_attr8 ,
pltd.tl_ext_attr9 ,
pltd.tl_ext_attr10 ,
pltd.tl_ext_attr11 ,
pltd.tl_ext_attr12 ,
pltd.tl_ext_attr13 ,
pltd.tl_ext_attr14 ,
pltd.tl_ext_attr15 ,
pltd.tl_ext_attr16 ,
pltd.tl_ext_attr17 ,
pltd.tl_ext_attr18 ,
pltd.tl_ext_attr19 ,
pltd.tl_ext_attr20 ,
pltd.tl_ext_attr21 ,
pltd.tl_ext_attr22 ,
pltd.tl_ext_attr23 ,
pltd.tl_ext_attr24 ,
pltd.tl_ext_attr25 ,
pltd.tl_ext_attr26 ,
pltd.tl_ext_attr27 ,
pltd.tl_ext_attr28 ,
pltd.tl_ext_attr29 ,
pltd.tl_ext_attr30 ,
pltd.tl_ext_attr31 ,
pltd.tl_ext_attr32 ,
pltd.tl_ext_attr33 ,
pltd.tl_ext_attr34 ,
pltd.tl_ext_attr35 ,
pltd.tl_ext_attr36 ,
pltd.tl_ext_attr37 ,
pltd.tl_ext_attr38 ,
pltd.tl_ext_attr39 ,
pltd.tl_ext_attr40 )
= ( SELECT
pltb.tl_ext_attr1 ,
pltb.tl_ext_attr2 ,
pltb.tl_ext_attr3 ,
pltb.tl_ext_attr4 ,
pltb.tl_ext_attr5 ,
pltb.tl_ext_attr6 ,
pltb.tl_ext_attr7 ,
pltb.tl_ext_attr8 ,
pltb.tl_ext_attr9 ,
pltb.tl_ext_attr10 ,
pltb.tl_ext_attr11 ,
pltb.tl_ext_attr12 ,
pltb.tl_ext_attr13 ,
pltb.tl_ext_attr14 ,
pltb.tl_ext_attr15 ,
pltb.tl_ext_attr16 ,
pltb.tl_ext_attr17 ,
pltb.tl_ext_attr18 ,
pltb.tl_ext_attr19 ,
pltb.tl_ext_attr20 ,
pltb.tl_ext_attr21 ,
pltb.tl_ext_attr22 ,
pltb.tl_ext_attr23 ,
pltb.tl_ext_attr24 ,
pltb.tl_ext_attr25 ,
pltb.tl_ext_attr26 ,
pltb.tl_ext_attr27 ,
pltb.tl_ext_attr28 ,
pltb.tl_ext_attr29 ,
pltb.tl_ext_attr30 ,
pltb.tl_ext_attr31 ,
pltb.tl_ext_attr32 ,
pltb.tl_ext_attr33 ,
pltb.tl_ext_attr34 ,
pltb.tl_ext_attr35 ,
pltb.tl_ext_attr36 ,
pltb.tl_ext_attr37 ,
pltb.tl_ext_attr38 ,
pltb.tl_ext_attr39 ,
pltb.tl_ext_attr40
FROM po_lines_all_ext_tl pltb --base
WHERE pltb.po_line_id = pltd.po_line_id
AND pltb.draft_id = -1
AND pltb.attr_group_id = pltd.attr_group_id
AND pltb.language = pltd.language
AND pltb.PK1_VALUE is null
)
WHERE pltd.po_line_id = p_po_line_s_no_lck_tbl(i)
AND pltd.draft_id = p_draft_id
AND pltd.PK1_VALUE is null;
PO_LOG.stmt(d_module,d_position ,'updated po_lines_all_ext_tl ');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
UPDATE po_line_locations_all_ext_b plld
SET ( plld.c_ext_attr1,
plld.c_ext_attr2 ,
plld.c_ext_attr3 ,
plld.c_ext_attr4 ,
plld.c_ext_attr5 ,
plld.c_ext_attr6 ,
plld.c_ext_attr7 ,
plld.c_ext_attr8 ,
plld.c_ext_attr9 ,
plld.c_ext_attr10 ,
plld.c_ext_attr11 ,
plld.c_ext_attr12 ,
plld.c_ext_attr13 ,
plld.c_ext_attr14 ,
plld.c_ext_attr15 ,
plld.c_ext_attr16 ,
plld.c_ext_attr17 ,
plld.c_ext_attr18 ,
plld.c_ext_attr19 ,
plld.c_ext_attr20 ,
plld.c_ext_attr21 ,
plld.c_ext_attr22 ,
plld.c_ext_attr23 ,
plld.c_ext_attr24 ,
plld.c_ext_attr25 ,
plld.c_ext_attr26 ,
plld.c_ext_attr27 ,
plld.c_ext_attr28 ,
plld.c_ext_attr29 ,
plld.c_ext_attr30 ,
plld.c_ext_attr31 ,
plld.c_ext_attr32 ,
plld.c_ext_attr33 ,
plld.c_ext_attr34 ,
plld.c_ext_attr35 ,
plld.c_ext_attr36 ,
plld.c_ext_attr37 ,
plld.c_ext_attr38 ,
plld.c_ext_attr39 ,
plld.c_ext_attr40 ,
plld.n_ext_attr1 ,
plld.n_ext_attr2 ,
plld.n_ext_attr3 ,
plld.n_ext_attr4 ,
plld.n_ext_attr5 ,
plld.n_ext_attr6 ,
plld.n_ext_attr7 ,
plld.n_ext_attr8 ,
plld.n_ext_attr9 ,
plld.n_ext_attr10 ,
plld.n_ext_attr11 ,
plld.n_ext_attr12 ,
plld.n_ext_attr13 ,
plld.n_ext_attr14 ,
plld.n_ext_attr15 ,
plld.n_ext_attr16 ,
plld.n_ext_attr17 ,
plld.n_ext_attr18 ,
plld.n_ext_attr19 ,
plld.n_ext_attr20 ,
plld.uom_ext_attr1 ,
plld.uom_ext_attr2 ,
plld.uom_ext_attr3 ,
plld.uom_ext_attr4,
plld.uom_ext_attr5 ,
plld.uom_ext_attr6 ,
plld.uom_ext_attr7 ,
plld.uom_ext_attr8 ,
plld.uom_ext_attr9 ,
plld.uom_ext_attr10 ,
plld.uom_ext_attr11 ,
plld.uom_ext_attr12 ,
plld.uom_ext_attr13 ,
plld.uom_ext_attr14 ,
plld.uom_ext_attr15 ,
plld.uom_ext_attr16 ,
plld.uom_ext_attr17 ,
plld.uom_ext_attr18 ,
plld.uom_ext_attr19 ,
plld.uom_ext_attr20 ,
plld.d_ext_attr1 ,
plld.d_ext_attr2 ,
plld.d_ext_attr3 ,
plld.d_ext_attr4 ,
plld.d_ext_attr5 ,
plld.d_ext_attr6 ,
plld.d_ext_attr7 ,
plld.d_ext_attr8 ,
plld.d_ext_attr9 ,
plld.d_ext_attr10 )
= ( SELECT
pllb.c_ext_attr1,
pllb.c_ext_attr2 ,
pllb.c_ext_attr3 ,
pllb.c_ext_attr4 ,
pllb.c_ext_attr5 ,
pllb.c_ext_attr6 ,
pllb.c_ext_attr7 ,
pllb.c_ext_attr8 ,
pllb.c_ext_attr9 ,
pllb.c_ext_attr10 ,
pllb.c_ext_attr11 ,
pllb.c_ext_attr12 ,
pllb.c_ext_attr13 ,
pllb.c_ext_attr14 ,
pllb.c_ext_attr15 ,
pllb.c_ext_attr16 ,
pllb.c_ext_attr17 ,
pllb.c_ext_attr18 ,
pllb.c_ext_attr19 ,
pllb.c_ext_attr20 ,
pllb.c_ext_attr21 ,
pllb.c_ext_attr22 ,
pllb.c_ext_attr23 ,
pllb.c_ext_attr24 ,
pllb.c_ext_attr25 ,
pllb.c_ext_attr26 ,
pllb.c_ext_attr27 ,
pllb.c_ext_attr28 ,
pllb.c_ext_attr29 ,
pllb.c_ext_attr30 ,
pllb.c_ext_attr31 ,
pllb.c_ext_attr32 ,
pllb.c_ext_attr33 ,
pllb.c_ext_attr34 ,
pllb.c_ext_attr35 ,
pllb.c_ext_attr36 ,
pllb.c_ext_attr37 ,
pllb.c_ext_attr38 ,
pllb.c_ext_attr39 ,
pllb.c_ext_attr40 ,
pllb.n_ext_attr1 ,
pllb.n_ext_attr2 ,
pllb.n_ext_attr3 ,
pllb.n_ext_attr4 ,
pllb.n_ext_attr5 ,
pllb.n_ext_attr6 ,
pllb.n_ext_attr7 ,
pllb.n_ext_attr8 ,
pllb.n_ext_attr9 ,
pllb.n_ext_attr10 ,
pllb.n_ext_attr11 ,
pllb.n_ext_attr12 ,
pllb.n_ext_attr13 ,
pllb.n_ext_attr14 ,
pllb.n_ext_attr15 ,
pllb.n_ext_attr16 ,
pllb.n_ext_attr17 ,
pllb.n_ext_attr18 ,
pllb.n_ext_attr19 ,
pllb.n_ext_attr20 ,
pllb.uom_ext_attr1 ,
pllb.uom_ext_attr2 ,
pllb.uom_ext_attr3 ,
pllb.uom_ext_attr4 ,
pllb.uom_ext_attr5 ,
pllb.uom_ext_attr6 ,
pllb.uom_ext_attr7 ,
pllb.uom_ext_attr8 ,
pllb.uom_ext_attr9 ,
pllb.uom_ext_attr10 ,
pllb.uom_ext_attr11 ,
pllb.uom_ext_attr12 ,
pllb.uom_ext_attr13 ,
pllb.uom_ext_attr14 ,
pllb.uom_ext_attr15 ,
pllb.uom_ext_attr16 ,
pllb.uom_ext_attr17 ,
pllb.uom_ext_attr18 ,
pllb.uom_ext_attr19 ,
pllb.uom_ext_attr20 ,
pllb.d_ext_attr1 ,
pllb.d_ext_attr2 ,
pllb.d_ext_attr3 ,
pllb.d_ext_attr4 ,
pllb.d_ext_attr5 ,
pllb.d_ext_attr6 ,
pllb.d_ext_attr7 ,
pllb.d_ext_attr8 ,
pllb.d_ext_attr9 ,
pllb.d_ext_attr10
FROM po_line_locations_all_ext_b pllb --base
WHERE pllb.line_location_id = plld.line_location_id
AND pllb.draft_id = -1
AND pllb.attr_group_id = plld.attr_group_id
)
WHERE plld.line_location_id = p_po_line_loc_s_no_lck_tbl(i)
AND plld.draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'Updated po_line_locations_all_ext_b for the given p_drat_id');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
UPDATE po_line_locations_all_ext_tl plltd
SET ( plltd.tl_ext_attr1 ,
plltd.tl_ext_attr2 ,
plltd.tl_ext_attr3 ,
plltd.tl_ext_attr4 ,
plltd.tl_ext_attr5 ,
plltd.tl_ext_attr6 ,
plltd.tl_ext_attr7 ,
plltd.tl_ext_attr8 ,
plltd.tl_ext_attr9 ,
plltd.tl_ext_attr10 ,
plltd.tl_ext_attr11 ,
plltd.tl_ext_attr12 ,
plltd.tl_ext_attr13 ,
plltd.tl_ext_attr14 ,
plltd.tl_ext_attr15 ,
plltd.tl_ext_attr16 ,
plltd.tl_ext_attr17 ,
plltd.tl_ext_attr18 ,
plltd.tl_ext_attr19 ,
plltd.tl_ext_attr20 ,
plltd.tl_ext_attr21 ,
plltd.tl_ext_attr22 ,
plltd.tl_ext_attr23 ,
plltd.tl_ext_attr24 ,
plltd.tl_ext_attr25 ,
plltd.tl_ext_attr26 ,
plltd.tl_ext_attr27 ,
plltd.tl_ext_attr28 ,
plltd.tl_ext_attr29 ,
plltd.tl_ext_attr30 ,
plltd.tl_ext_attr31 ,
plltd.tl_ext_attr32 ,
plltd.tl_ext_attr33 ,
plltd.tl_ext_attr34 ,
plltd.tl_ext_attr35 ,
plltd.tl_ext_attr36 ,
plltd.tl_ext_attr37 ,
plltd.tl_ext_attr38 ,
plltd.tl_ext_attr39 ,
plltd.tl_ext_attr40 )
= ( SELECT
plltb.tl_ext_attr1 ,
plltb.tl_ext_attr2 ,
plltb.tl_ext_attr3 ,
plltb.tl_ext_attr4 ,
plltb.tl_ext_attr5 ,
plltb.tl_ext_attr6 ,
plltb.tl_ext_attr7 ,
plltb.tl_ext_attr8 ,
plltb.tl_ext_attr9 ,
plltb.tl_ext_attr10 ,
plltb.tl_ext_attr11 ,
plltb.tl_ext_attr12 ,
plltb.tl_ext_attr13 ,
plltb.tl_ext_attr14 ,
plltb.tl_ext_attr15 ,
plltb.tl_ext_attr16 ,
plltb.tl_ext_attr17 ,
plltb.tl_ext_attr18 ,
plltb.tl_ext_attr19 ,
plltb.tl_ext_attr20 ,
plltb.tl_ext_attr21 ,
plltb.tl_ext_attr22 ,
plltb.tl_ext_attr23 ,
plltb.tl_ext_attr24 ,
plltb.tl_ext_attr25 ,
plltb.tl_ext_attr26 ,
plltb.tl_ext_attr27 ,
plltb.tl_ext_attr28 ,
plltb.tl_ext_attr29 ,
plltb.tl_ext_attr30 ,
plltb.tl_ext_attr31 ,
plltb.tl_ext_attr32 ,
plltb.tl_ext_attr33 ,
plltb.tl_ext_attr34 ,
plltb.tl_ext_attr35 ,
plltb.tl_ext_attr36 ,
plltb.tl_ext_attr37 ,
plltb.tl_ext_attr38 ,
plltb.tl_ext_attr39 ,
plltb.tl_ext_attr40
FROM po_line_locations_all_ext_tl plltb --base
WHERE plltb.line_location_id = plltd.line_location_id
AND plltb.draft_id = -1
AND plltb.attr_group_id = plltd.attr_group_id
AND plltb.language = plltd.language
)
WHERE plltd.line_location_id = p_po_line_loc_s_no_lck_tbl(i)
AND plltd.draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position ,'Updated po_line_locations_all_ext_tl for the given p_drat_id');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = p_draft_id;
UPDATE po_headers_all_ext_b phbd
SET ( phbd.c_ext_attr1,
phbd.c_ext_attr2 ,
phbd.c_ext_attr3 ,
phbd.c_ext_attr4 ,
phbd.c_ext_attr5 ,
phbd.c_ext_attr6 ,
phbd.c_ext_attr7 ,
phbd.c_ext_attr8 ,
phbd.c_ext_attr9 ,
phbd.c_ext_attr10 ,
phbd.c_ext_attr11 ,
phbd.c_ext_attr12 ,
phbd.c_ext_attr13 ,
phbd.c_ext_attr14 ,
phbd.c_ext_attr15 ,
phbd.c_ext_attr16 ,
phbd.c_ext_attr17 ,
phbd.c_ext_attr18 ,
phbd.c_ext_attr19 ,
phbd.c_ext_attr20 ,
phbd.c_ext_attr21 ,
phbd.c_ext_attr22 ,
phbd.c_ext_attr23 ,
phbd.c_ext_attr24 ,
phbd.c_ext_attr25 ,
phbd.c_ext_attr26 ,
phbd.c_ext_attr27 ,
phbd.c_ext_attr28 ,
phbd.c_ext_attr29 ,
phbd.c_ext_attr30 ,
phbd.c_ext_attr31 ,
phbd.c_ext_attr32 ,
phbd.c_ext_attr33 ,
phbd.c_ext_attr34 ,
phbd.c_ext_attr35 ,
phbd.c_ext_attr36 ,
phbd.c_ext_attr37 ,
phbd.c_ext_attr38 ,
phbd.c_ext_attr39 ,
phbd.c_ext_attr40 ,
phbd.n_ext_attr1 ,
phbd.n_ext_attr2 ,
phbd.n_ext_attr3 ,
phbd.n_ext_attr4 ,
phbd.n_ext_attr5 ,
phbd.n_ext_attr6 ,
phbd.n_ext_attr7 ,
phbd.n_ext_attr8 ,
phbd.n_ext_attr9 ,
phbd.n_ext_attr10 ,
phbd.n_ext_attr11 ,
phbd.n_ext_attr12 ,
phbd.n_ext_attr13 ,
phbd.n_ext_attr14 ,
phbd.n_ext_attr15 ,
phbd.n_ext_attr16 ,
phbd.n_ext_attr17 ,
phbd.n_ext_attr18 ,
phbd.n_ext_attr19 ,
phbd.n_ext_attr20 ,
phbd.uom_ext_attr1 ,
phbd.uom_ext_attr2 ,
phbd.uom_ext_attr3 ,
phbd.uom_ext_attr4,
phbd.uom_ext_attr5 ,
phbd.uom_ext_attr6 ,
phbd.uom_ext_attr7 ,
phbd.uom_ext_attr8 ,
phbd.uom_ext_attr9 ,
phbd.uom_ext_attr10 ,
phbd.uom_ext_attr11 ,
phbd.uom_ext_attr12 ,
phbd.uom_ext_attr13 ,
phbd.uom_ext_attr14 ,
phbd.uom_ext_attr15 ,
phbd.uom_ext_attr16 ,
phbd.uom_ext_attr17 ,
phbd.uom_ext_attr18 ,
phbd.uom_ext_attr19 ,
phbd.uom_ext_attr20 ,
phbd.d_ext_attr1 ,
phbd.d_ext_attr2 ,
phbd.d_ext_attr3 ,
phbd.d_ext_attr4 ,
phbd.d_ext_attr5 ,
phbd.d_ext_attr6 ,
phbd.d_ext_attr7 ,
phbd.d_ext_attr8 ,
phbd.d_ext_attr9 ,
phbd.d_ext_attr10 ,
phbd.revision_num)
= ( SELECT
phb.c_ext_attr1,
phb.c_ext_attr2 ,
phb.c_ext_attr3 ,
phb.c_ext_attr4 ,
phb.c_ext_attr5 ,
phb.c_ext_attr6 ,
phb.c_ext_attr7 ,
phb.c_ext_attr8 ,
phb.c_ext_attr9 ,
phb.c_ext_attr10 ,
phb.c_ext_attr11 ,
phb.c_ext_attr12 ,
phb.c_ext_attr13 ,
phb.c_ext_attr14 ,
phb.c_ext_attr15 ,
phb.c_ext_attr16 ,
phb.c_ext_attr17 ,
phb.c_ext_attr18 ,
phb.c_ext_attr19 ,
phb.c_ext_attr20 ,
phb.c_ext_attr21 ,
phb.c_ext_attr22 ,
phb.c_ext_attr23 ,
phb.c_ext_attr24 ,
phb.c_ext_attr25 ,
phb.c_ext_attr26 ,
phb.c_ext_attr27 ,
phb.c_ext_attr28 ,
phb.c_ext_attr29 ,
phb.c_ext_attr30 ,
phb.c_ext_attr31 ,
phb.c_ext_attr32 ,
phb.c_ext_attr33 ,
phb.c_ext_attr34 ,
phb.c_ext_attr35 ,
phb.c_ext_attr36 ,
phb.c_ext_attr37 ,
phb.c_ext_attr38 ,
phb.c_ext_attr39 ,
phb.c_ext_attr40 ,
phb.n_ext_attr1 ,
phb.n_ext_attr2 ,
phb.n_ext_attr3 ,
phb.n_ext_attr4 ,
phb.n_ext_attr5 ,
phb.n_ext_attr6 ,
phb.n_ext_attr7 ,
phb.n_ext_attr8 ,
phb.n_ext_attr9 ,
phb.n_ext_attr10 ,
phb.n_ext_attr11 ,
phb.n_ext_attr12 ,
phb.n_ext_attr13 ,
phb.n_ext_attr14 ,
phb.n_ext_attr15 ,
phb.n_ext_attr16 ,
phb.n_ext_attr17 ,
phb.n_ext_attr18 ,
phb.n_ext_attr19 ,
phb.n_ext_attr20 ,
phb.uom_ext_attr1 ,
phb.uom_ext_attr2 ,
phb.uom_ext_attr3 ,
phb.uom_ext_attr4 ,
phb.uom_ext_attr5 ,
phb.uom_ext_attr6 ,
phb.uom_ext_attr7 ,
phb.uom_ext_attr8 ,
phb.uom_ext_attr9 ,
phb.uom_ext_attr10 ,
phb.uom_ext_attr11 ,
phb.uom_ext_attr12 ,
phb.uom_ext_attr13 ,
phb.uom_ext_attr14 ,
phb.uom_ext_attr15 ,
phb.uom_ext_attr16 ,
phb.uom_ext_attr17 ,
phb.uom_ext_attr18 ,
phb.uom_ext_attr19 ,
phb.uom_ext_attr20 ,
phb.d_ext_attr1 ,
phb.d_ext_attr2 ,
phb.d_ext_attr3 ,
phb.d_ext_attr4 ,
phb.d_ext_attr5 ,
phb.d_ext_attr6 ,
phb.d_ext_attr7 ,
phb.d_ext_attr8 ,
phb.d_ext_attr9 ,
phb.d_ext_attr10 ,
phb.revision_num
FROM po_headers_all_ext_b phb--base
WHERE phb.po_header_id = phbd.po_header_id
AND phb.draft_id = -1
AND phb.attr_group_id = phbd.attr_group_id
AND phb.c_ext_attr39=phbd.c_ext_attr39
)
WHERE phbd.po_header_id = l_po_header_id
AND phbd.draft_id = p_draft_id
-- include addresses
AND phbd.c_ext_attr40 ='PO_UDA_ADDRESS_TYPES'
-- include multi-row attribute groups
AND EXISTS (SELECT 'Multi row attr grp'
FROM ego_fnd_dsc_flx_ctx_ext ag
WHERE phbd.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'Y')
AND NOT EXISTS (SELECT 'only S or Null Locks'
FROM po_entity_locks pel
WHERE pel.lock_type IN ('P','F')
AND pel.entity_pk1= phbd.po_header_id
AND pel.entity_pk2= phbd.c_ext_attr39
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_ADDRESS_ENTITY
AND pel.lock_by_draft_id = p_draft_id )
AND EXISTS (SELECT 'base.rev_num>draft.rev_num'
FROM po_headers_all_ext_b phb,
po_drafts dft
WHERE ( (p_ignore_rev_number ='Y') OR (NVL(phb.revision_num,0) > NVL(phbd.revision_num,0) OR NVL(dft.revision_num,0) = -1))
AND phb.draft_id = -1
AND phb.po_header_id=l_po_header_id
and phbd.draft_id = dft.draft_id);
PO_LOG.stmt(d_module,d_position ,'Updated UDA_ADDRESS');
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT revision_num
INTO l_base_rev_num
FROM po_headers_all
WHERE po_header_id = l_document_id;
SELECT revision_num
INTO l_mod_rev_num
FROM po_headers_draft_all
WHERE po_header_id = l_document_id
AND draft_id = l_draftid;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = l_draft_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
INSERT INTO po_entity_locks(
po_entity_lock_id,
entity_name,
entity_pk1,
lock_by_draft_id,
lock_type,
last_update_date,
last_updated_by,
creation_date,
created_by
)
VALUES(
po_entity_locks_s.NEXTVAL,
G_MOD_SYNC_LOCK,
l_po_header_id,
l_draft_id,
'F',
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id
);
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = l_draft_id;
SELECT 'N'
INTO l_is_lock_available
FROM po_entity_locks
WHERE entity_pk1 = l_po_header_id
AND entity_name = G_MOD_SYNC_LOCK
AND lock_by_draft_id <> l_draft_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = l_draft_id;
SELECT revision_num INTO l_draft_rev_num FROM po_headers_draft_all WHERE po_header_id = l_po_header_id AND draft_id = l_draft_id;
SELECT revision_num INTO l_header_base_rev_num FROM po_headers_all WHERE po_header_id = l_po_header_id;
SELECT revision_num INTO draft_revision_num FROM po_drafts WHERE draft_id = l_draft_id;
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = l_draft_id;
SELECT revision_num into l_revision_number FROM po_headers_all WHERE po_header_id = l_po_header_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select pf.DOC_FORMAT_TMPL_CODE, f.iso_territory, f.language_code
into l_template_code,l_cd_territory,l_lang_code
from po_print_form_formats pf, po_drafts h, po_headers_all h1, fnd_languages f
where pf.FORM_SOURCE = 'PO' and pf.DOCUMENT_TYPE = 'PO_MOD_STD_FORM' and pf.STANDARD_FORM = h.CLM_STANDARD_FORM
and pf.DOCUMENT_FORMAT = h.CLM_DOCUMENT_FORMAT and h1.po_header_id = h.document_id
and pf.STYLE_ID = h1.STYLE_ID and h.draft_id = l_draft_id and f.language_code = USERENV('LANG');
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT item_key FROM wf_item_activity_statuses_v
WHERE user_key = (SELECT segment1 FROM po_headers_all
WHERE po_header_id = (SELECT document_id
FROM po_drafts
WHERE draft_id = l_draft_id))
AND activity_name = 'WAIT_FOR_LOCK_TO_BE_RELEASED' AND activity_status_code = 'NOTIFIED' ;
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = l_draft_id;
delete
FROM po_entity_locks
WHERE lock_by_draft_id = l_draft_id
AND entity_pk1 = l_po_header_id
AND entity_name = G_MOD_SYNC_LOCK;
x_progress := 'PO_MOD_SYNC_PROCESS_PVT.RELEASE_SYNC_SGD_LOCK: No Of rows deleted from po_entity_locks table : ' || SQL%ROWCOUNT;
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
po_wf_debug_pkg.Insert_debug(itemtype, itemkey, x_progress);