Format Payment Instructions Useful Scripts

Payment Templates – Details of Payment Templates defined:

 SELECT apt.template_id, apt.template_name,

 ipmv.payment_method_name pmt_method_name,

 cba.bank_account_name bank_acct_name,

 ipp.payment_profile_name pmt_profile_name,

 cpd.payment_document_name pmt_doc_name, apt.pay_group_option pay_grp_optn,

 apt.ou_group_option ou_grp_optn, apt.currency_group_option curr_grp_optn,

 apt.description ppp_description, apt.inactive_date, apt.addl_pay_thru_days,

 apt.addl_pay_from_days, apt.low_payment_priority, apt.hi_payment_priority,

 apt.vendor_id, apt.pay_only_when_due_flag,

 apt.vendor_type_lookup_code vdr_type_lcode, apt.bank_account_id,

 apt.payment_profile_id, apt.zero_inv_allowed_flag, apt.payment_method_code,

 apt.inv_exchange_rate_type, apt.payment_date_option, apt.addl_payment_days,

 apt.payment_exchange_rate_type, apt.zero_amounts_allowed,

 apt.payables_review_settings, apt.calc_awt_int_flag, apt.payments_review_settings,

 apt.document_rejection_level_code doc_reject_lvl, apt.create_instrs_flag,

 apt.payment_rejection_level_code pmt_reject_lvl, apt.payment_document_id,

 plc.displayed_field supplier_type, pv.vendor_name payee,

 alc1.displayed_field template_type_name, gdct.user_conversion_type user_rate_type,

 fu.user_name

 FROM ap_payment_templates apt, po_lookup_codes plc, iby_payment_methods_vl ipmv,

 iby_payment_profiles ipp, ce_bank_accounts cba, ap_lookup_codes alc1,

 gl_daily_conversion_types gdct, po_vendors pv, fnd_user fu, ce_payment_documents cpd

 WHERE fu.user_id = apt.last_updated_by

 AND plc.lookup_code(+) = apt.vendor_type_lookup_code

 AND plc.lookup_type(+) = 'VENDOR TYPE'

 AND cba.bank_account_id(+) = apt.bank_Account_id

 AND ipmv.payment_method_code(+) = apt.payment_method_code

 AND alc1.lookup_type(+) = 'PAYMENT_TEMPLATE_TYPE'

 AND alc1.lookup_code(+) = apt.template_type

 AND gdct.conversion_type(+) = apt.payment_exchange_rate_type

 AND ipp.payment_profile_id(+) = apt.payment_profile_id

 AND pv.party_id(+) = apt.party_id

 AND apt.payment_document_id = cpd.payment_document_id(+)

 -- Uncomment to limit to specific Payment Templates

 -- AND apt.TEMPLATE_NAME LIKE 'J%'

ORDER BY apt.template_name, ipmv.payment_method_name;

 Payment Process Profiles – Verification of your PPP setup:

SELECT app.payment_profile_name, ppp.system_profile_code, ppp.payment_format_code,

 pm.payment_method_name, ppp.periodic_sequence_name_1 seq_name,

 app.payment_profile_id pmt_prof, app.reset_value_1 reset_val,

 app.last_used_number_1 last_used, ppp.processing_type,

 ppp.mark_complete_event mrk_cmplt, ppp.outbound_pmt_file_directory,

 ppp.outbound_pmt_file_prefix, ppp.outbound_pmt_file_extension ext,

 ppp.positive_pay_file_directory, ppp.positive_pay_file_prefix,

 ppp.positive_pay_file_extension pp_ext, ppp.default_payment_document_id def_pmt_docid,

 ppp.last_update_date, sra.sra_override_payee_flag sra_ovrd_payee,

 fmt.format_name sra_fmt, sra.automatic_sra_submit_flag sra_autosub

 FROM iby_sys_pmt_profiles_b ppp, iby_acct_pmt_profiles_vl app,

 iby_applicable_pmt_profs appl_prof, iby_payment_methods_vl pm,

 iby_remit_advice_setup sra, iby_formats_vl fmt

WHERE 1 = 1

 AND ppp.system_profile_code = app.system_profile_code

 AND ppp.system_profile_code = appl_prof.system_profile_code(+)

 AND appl_prof.applicable_value_to = pm.payment_method_code(+)

 AND NVL (appl_prof.applicable_type_code, 'PAYMENT_METHOD') = 'PAYMENT_METHOD'

 AND ppp.system_profile_code = sra.system_profile_code(+)

 AND sra.remittance_advice_format_code = fmt.format_code(+)

 -- Only custom PPP’s, if this is your naming convention

 AND ppp.system_profile_code LIKE 'XX%';

Payment Documents – Setup data, by internal disbursing Bank Account:

 SELECT acct.bank_account_name, acct.bank_account_num, acct.currency_code,

 acct.multi_currency_allowed_flag multi_curr, acct.payment_multi_currency_flag pmt_mc,

 pmtdoc.payment_document_name pmt_doc_name, fmt.format_name,

 pmtdoc.first_available_document_num first_avail_docno,

 pmtdoc.last_available_document_number last_avail_docno,

 pmtdoc.last_issued_document_number last_issued_docno,

 pmtdoc.payment_document_id pmt_doc_id, pmtdoc.internal_bank_account_id,

 pmtdoc.paper_stock_type, pmtdoc.attached_remittance_stub_flag attchd_stub,

 pmtdoc.number_of_lines_per_remit_stub lines_per_stub, pmtdoc.format_code,

 pmtdoc.inactive_date, fu1.user_name created_by, pmtdoc.creation_date,

 fu2.user_name last_updated_by, pmtdoc.last_update_date,

 DECODE (inactive_date, NULL, 'Y', 'N') status

 FROM ce_payment_documents pmtdoc, iby_formats_vl fmt, ce_bank_accounts acct,

 fnd_user fu1, fnd_user fu2

 WHERE pmtdoc.format_code = fmt.format_code

 AND pmtdoc.internal_bank_account_id = acct.bank_account_id

 AND pmtdoc.created_by = fu1.user_id

 AND pmtdoc.last_updated_by = fu2.user_id

 -- Uncomment to limit to a certain document name

 -- AND payment_document_name LIKE '%'

ORDER BY acct.bank_account_name, pmtdoc.payment_document_name;

Layout Templates - Metadata and RTF/eText files for all Outbound Payment Instruction Layout Templates:

SELECT xtv.application_short_name tmpl_app, xtv.template_code,

 xtv.ds_app_short_name ds_app, xtv.data_source_code, xtv.template_type_code tmpl_type,

 xtv.default_language def_lang, xtv.default_territory def_terr, xtv.template_status,

 xtv.start_date, xtv.end_date, xtv.template_name, xtv.description, xtv.created_by,

 xtv.creation_date, xtv.last_updated_by, xtv.last_update_date, xtv.last_update_login,

 (SELECT application_name

 FROM fnd_application_vl

 WHERE application_short_name = xtv.application_short_name) application_name,

 (SELECT meaning

 FROM fnd_lookups

 WHERE lookup_type = 'XDO_TEMPLATE_TYPE'

 AND lookup_code = xtv.template_type_code) template_type,

 (SELECT data_source_name

 FROM xdo_ds_definitions_vl

 WHERE data_source_code = xtv.data_source_code

 AND application_short_name = xtv.ds_app_short_name) data_source_name,

 (SELECT file_name

 FROM xdo_lobs

 WHERE ( ( lob_type = 'TEMPLATE'

 AND xdo_file_type != 'RTF'

 AND xdo_file_type = xtv.template_type_code

 AND xdo_lobs.LANGUAGE = xtv.default_language

 AND xdo_lobs.territory = xtv.default_territory)

 OR ( lob_type = 'TEMPLATE_SOURCE'

 AND xdo_file_type IN ('RTF', 'RTF-ETEXT')

 AND xdo_lobs.LANGUAGE = xtv.default_language

 AND xdo_lobs.territory = xtv.default_territory)

 OR ( xdo_file_type = 'RTF'

 AND lob_type = 'TEMPLATE_SOURCE'

 AND LANGUAGE = xtv.mls_language

 AND territory = xtv.mls_territory

 AND EXISTS

 (SELECT mls.lob_code

 FROM xdo_lobs mls

 WHERE mls.lob_type = 'MLS_TEMPLATE'

 AND mls.lob_code = xtv.template_code

 AND mls.application_short_name = xtv.application_short_name

 AND mls.LANGUAGE = xtv.default_language

 AND mls.territory = xtv.default_territory)

 AND NOT EXISTS

 (SELECT LOCAL.lob_code

 FROM xdo_lobs LOCAL

 WHERE LOCAL.lob_type = 'TEMPLATE_SOURCE'

 AND LOCAL.lob_code = xtv.template_code

 AND LOCAL.application_short_name = xtv.application_short_name

 AND LOCAL.LANGUAGE = xtv.default_language

 AND LOCAL.territory = xtv.default_territory)))

 AND lob_code = xtv.template_code

 AND xdo_lobs.application_short_name = xtv.application_short_name)

 AS default_template_file,

 (SELECT file_name FROM xdo_lobs

 WHERE lob_type = 'TEMPLATE_SOURCE' AND lob_code = xtv.template_code

 AND xdo_lobs.application_short_name =xtv.application_short_name

 AND xdo_lobs.LANGUAGE = xtv.mls_language

 AND xdo_lobs.territory = xtv.mls_territory) AS mls_template_file,

 (SELECT NAME

 FROM fnd_iso_languages_vl

 WHERE iso_language_2 = xtv.default_language) AS default_file_lang,

 DECODE (xtv.default_territory, '00', '', ftv.territory_short_name) AS default_file_terr,

 xtv.mls_language, xtv.mls_territory, xtv.default_output_type

 FROM xdo_templates_vl xtv, fnd_application_vl fav, fnd_territories_vl ftv

WHERE 1 = 1

 AND fav.application_short_name = xtv.application_short_name

 AND ftv.territory_code(+) = xtv.default_territory

 -- Only Outbound Payment Instructions

 AND xtv.application_short_name = 'IBY'

 AND data_source_code = 'IBY_FD_INSTRUCTION_1_0';

PPR and Source XML – Payment Process Requests formatted today, with the source XML:

SELECT trxnmid,

 EXTRACTVALUE (

 XMLType (document),

 '/OutboundPaymentInstruction/PaymentInstructionInfo/UserAssignedRefCode') ppr_name,

 EXTRACTVALUE (

 XMLType (document),

 '/OutboundPaymentInstruction/PaymentInstructionInfo/FormatProgramRequestID')

 fmt_request_id,

 EXTRACTVALUE (

 XMLType (document),

 '/OutboundPaymentInstruction/PaymentProcessProfile/PaymentProcessProfileName')

 ppp_name,

 EXTRACTVALUE (

 XMLType (document),

 '/OutboundPaymentInstruction/CheckFormatInfo/PaymentDocumentName')

 pmt_doc_name,

 EXTRACTVALUE (

 XMLType (document),

 '/OutboundPaymentInstruction/InstructionTotals/PaymentCount') pmt_cnt,

 document raw_xml

 FROM iby_trxn_documents itd

WHERE 1 = 1

 AND itd.creation_date > TRUNC (SYSDATE);

Script to Fix Items showing Negative Qty on ONhand Quantity Form in Oracle.

Script to Fix Items showing Negative Qty on ONhand Quantity Form in Oracle.

The Script will perform
 script will get rid of outstanding reservation records that exist for order lines which are closed or cancelled.For both external sales orders and internal orders.Also script cleans reservations left after Order line is deleted but reservations exist.Script deletes from mtl_demand which are not in sync with Mtl_reservations.

SELECT  V.PROFILE_OPTION_VALUE OE_PROFILE
FROM    FND_PROFILE_OPTION_VALUES V
WHERE   (V.PROFILE_OPTION_ID, V.APPLICATION_ID, V.LEVEL_ID) =
                (SELECT V2.PROFILE_OPTION_ID,V2.APPLICATION_ID,MAX(V2.LEVEL_ID)
                FROM FND_PROFILE_OPTIONS OO,
                     FND_PROFILE_OPTION_VALUES V2
                WHERE ((V2.LEVEL_ID = 10001 AND V2.LEVEL_VALUE=0)
                          OR (V2.LEVEL_ID = 10002 AND V2.LEVEL_VALUE=660))
                AND   OO.PROFILE_OPTION_ID = V2.PROFILE_OPTION_ID
                AND   OO.APPLICATION_ID = V2.APPLICATION_ID
                AND   OO.APPLICATION_ID = 660
                AND   UPPER(OO.PROFILE_OPTION_NAME) = 'ONT_SOURCE_CODE'
                GROUP BY V2.PROFILE_OPTION_ID, V2.APPLICATION_ID)
AND   V.LEVEL_VALUE = DECODE(V.LEVEL_ID, 10001, 0, 10002, 660);

PROMPT ** This gets rid of extraneous demand and reservations that are left
PROMPT ** after a line has been cancelled  

SELECT L.LINE_ID, L.HEADER_ID
FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M
WHERE  M.PRIMARY_RESERVATION_QUANTITY>0
AND nvl(L.CANCELLED_FLAG,'N')='Y'
AND L.CANCELLED_QUANTITY IS NOT NULL
AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
                WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
                AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID
                AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE');


UPDATE MTL_RESERVATIONS
SET    PRIMARY_RESERVATION_QUANTITY = 0
,      RESERVATION_QUANTITY = 0
,      LAST_UPDATED_BY=-2471362
WHERE  PRIMARY_RESERVATION_QUANTITY>0
AND    EXISTS (SELECT 'X'
               FROM   OE_ORDER_LINES_ALL L
               WHERE  nvl(L.CANCELLED_FLAG,'N')='Y'
               AND L.CANCELLED_QUANTITY IS NOT NULL
               AND L.LINE_ID = MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID
               AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
                               WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
                               AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID
                               AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE'));



PROMPT ** This gets rid of extraneous demand and reservations that are left
PROMPT ** after a line has been closed or delivery detail is cancelled

SELECT L.LINE_ID, L.HEADER_ID
FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M
WHERE   M.PRIMARY_RESERVATION_QUANTITY>0
AND nvl(L.OPEN_FLAG,'Y')='N'
AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
                WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
                AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID
                AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE')
AND NOT EXISTS    (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD
                 WHERE WDD.SOURCE_LINE_ID=L.LINE_ID
                   AND WDD.SOURCE_CODE ='OE'
                   AND WDD.INV_INTERFACED_FLAG IN ('N','P')
                   AND WDD.RELEASED_STATUS <> 'D');


UPDATE MTL_RESERVATIONS
SET    PRIMARY_RESERVATION_QUANTITY = 0
,      RESERVATION_QUANTITY = 0
,      LAST_UPDATED_BY=-2471362
WHERE  PRIMARY_RESERVATION_QUANTITY>0
AND    EXISTS (SELECT 'X'
               FROM   OE_ORDER_LINES_ALL L
               WHERE  nvl(L.OPEN_FLAG,'Y')='N'
               AND L.LINE_ID = nvl(MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID,-99)
               AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
                               WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
                               AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID
                               AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE')
               AND NOT EXISTS    (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD
                 WHERE WDD.SOURCE_LINE_ID=L.LINE_ID
                   AND WDD.SOURCE_CODE ='OE'
                   AND WDD.INV_INTERFACED_FLAG IN ('N','P')
                   AND WDD.RELEASED_STATUS <> 'D'));



PROMPT ORDER DETAILS WITH ORPHAN RESERVATIONS AFTER LINE IS DELETED

SELECT MSO.SEGMENT1 ORD_NUMBER,
       MSO.SEGMENT2 ORD_TYPE,
       MSO.SALES_ORDER_ID sALES_ORDER_ID,
       MR.DEMAND_SOURCE_LINE_ID oRDER_LINE_ID,
       MR.iNVENTORY_ITEM_ID iTEM_ID,
       MR.ORGANIZATION_ID ORGANIZATION_ID,
       MR.PRIMARY_RESERVATION_QUANTITY pRSV_QTY,
       MR.RESERVATION_QUANTITY RSV_QTY
 FROM MTL_RESERVATIONS MR,
      MTL_SALES_ORDERS MSO
WHERE MSO.SALES_ORDER_ID=MR.DEMAND_SOURCE_HEADER_ID
  AND MR.DEMAND_SOURCE_TYPE_ID IN (2,8)
  AND MR.DEMAND_SOURCE_LINE_ID NOT IN (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
                                         WHERE LINE_ID=MR.DEMAND_SOURCE_LINE_ID );


PROMPT UPDATING MTL_RESERVATIONS

UPDATE MTL_RESERVATIONS MR
SET PRIMARY_RESERVATION_QUANTITY=0,
    RESERVATION_QUANTITY=0,
    LAST_UPDATED_BY=-2471362
   WHERE MR.DEMAND_SOURCE_TYPE_ID IN (2,8)
     AND MR.DEMAND_SOURCE_LINE_ID NOT IN (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
                                            WHERE LINE_ID=MR.DEMAND_SOURCE_LINE_ID);
COMMIT;

PROMPT **Updating records for closed /cancelled lines in mtl_demand where records are not in sync
UPDATE MTL_DEMAND D
SET    PRIMARY_UOM_QUANTITY = 0
,      LINE_ITEM_QUANTITY   = 0
,      COMPLETED_QUANTITY   = 0
,      LINE_ITEM_RESERVATION_QTY = 0
,      RESERVATION_QUANTITY = 0
,      LAST_UPDATED_BY=-2471362
WHERE  DEMAND_SOURCE_TYPE IN (2,8)
AND RESERVATION_TYPE = 2
AND    DEMAND_SOURCE_LINE = ( SELECT O.LINE_ID
                                FROM OE_ORDER_LINES_ALL O
                                WHERE  ( NVL(o.OPEN_FLAG,'Y') = 'N'
                                         OR NVL(O.CANCELLED_FLAG,'N') ='Y')
                                AND  O.LINE_ID =D.DEMAND_SOURCE_LINE )
AND   DEMAND_SOURCE_LINE NOT IN  ( select TRX_SOURCE_LINE_ID
                    FROM  MTL_TRANSACTIONS_INTERFACE MTI
                    WHERE  MTI.SOURCE_LINE_ID = D.DEMAND_SOURCE_LINE)
AND DEMAND_SOURCE_LINE NOT IN (SELECT SOURCE_LINE_ID
                   FROM WSH_DELIVERY_DETAILS WDD
                 WHERE WDD.SOURCE_LINE_ID=D.DEMAND_SOURCE_LINE
                   AND WDD.SOURCE_CODE='OE'
                   AND WDD.INV_INTERFACED_FLAG IN ('N','P'));

PROMPT FINAL UPDATING MTL_DEMAND

UPDATE MTL_DEMAND SET PRIMARY_UOM_QUANTITY=0,
                      COMPLETED_QUANTITY=0,
                      RESERVATION_QUANTITY=0,
                      LAST_UPDATED_BY=-2471362
       WHERE DEMAND_SOURCE_TYPE IN (2,8)
         AND RESERVATION_TYPE=2
         AND DEMAND_ID IN (SELECT N_COLUMN1 FROM MTL_RESERVATIONS WHERE
                                    DEMAND_SOURCE_TYPE_ID IN (2,8)
                                AND PRIMARY_RESERVATION_QUANTITY=0
                                AND RESERVATION_QUANTITY=0
                                AND LAST_UPDATED_BY=-2471362);



PROMPT **Deleting reservations which are complete

COMMIT;

DELETE FROM MTL_RESERVATIONS WHERE
       DEMAND_SOURCE_TYPE_ID IN (2,8)
 AND   LAST_UPDATED_BY=-2471362;


PROMPT **Deleting records from mtl_demand where reservations are complete

DELETE FROM MTL_DEMAND WHERE
    DEMAND_SOURCE_TYPE IN (2,8)
AND RESERVATION_TYPE=2     
AND LAST_UPDATED_BY=-2471362;