Receivables - Payment Terms

Oracle Release

12.1.3

 

Setup : Transactions : Payment Terms

 

SELECT RTT.NAME "Name",

       RTT.DESCRIPTION "Description",

       RT.PARTIAL_DISCOUNT_FLAG "Allow Discounts on Part. Pay.",

       RT.PREPAYMENT_FLAG "Prepayment",

       RT.CREDIT_CHECK_FLAG "Credit Check",

       ACBCT.CYCLE_NAME "Billing Cycle",

       RT.BASE_AMOUNT "Base Amount",

       DECODE (RT.CALC_DISCOUNT_ON_LINES_FLAG, 'N', 'Invoice Amount', FLV_DISC.MEANING) "Discount Basis",

       TO_CHAR(RT.START_DATE_ACTIVE, 'DD-MON-RRRR') || ' - ' ||

       TO_CHAR(RT.END_DATE_ACTIVE, 'DD-MON-RRRR') "Effective Dates",

       RT.PRINTING_LEAD_DAYS "Print Lead Days",

       FLV_INST.MEANING "Installment Options",

       RTL.SEQUENCE_NUM "Seq",

       RTL.RELATIVE_AMOUNT "Relative Amount",

       RTL.DUE_DAYS "Days",

       RTL.DUE_DATE "Date",

       RTL.DUE_DAY_OF_MONTH "Day of Month",

       RTL.DUE_MONTHS_FORWARD "Months Ahead"

  FROM APPS.RA_TERMS_TL            RTT,

       APPS.RA_TERMS_B             RT,

       APPS.AR_CONS_BILL_CYCLES_TL ACBCT,

       APPS.RA_TERMS_LINES         RTL,

       APPS.FND_LOOKUP_VALUES      FLV_DISC,

       APPS.FND_LOOKUP_VALUES      FLV_INST

 WHERE 1 = 1

   AND RT.TERM_ID = RTT.TERM_ID(+)

   AND RTT.LANGUAGE = RTT.SOURCE_LANG

   AND RT.BILLING_CYCLE_ID = ACBCT.BILLING_CYCLE_ID(+)

   AND ACBCT.LANGUAGE(+) = 'US'

   AND RT.TERM_ID = RTL.TERM_ID

   AND RT.CALC_DISCOUNT_ON_LINES_FLAG = FLV_DISC.LOOKUP_CODE(+)

   AND FLV_DISC.LOOKUP_TYPE(+) = 'DISCOUNT_BASIS'

   AND FLV_DISC.LANGUAGE(+) = 'US'

   AND RT.FIRST_INSTALLMENT_CODE = FLV_INST.LOOKUP_CODE(+)

   AND FLV_INST.LOOKUP_TYPE(+) = 'INSTALLMENT_OPTION'

   AND FLV_INST.LANGUAGE(+) = 'US'

   AND RT.LAST_UPDATED_BY NOT IN DECODE(NVL(&EXCLUD_SEEDED, 'N'), 'Y', 1, -999)

 ORDER BY RTT.NAME, RTL.SEQUENCE_NUM

 

Filters

NAME	POSSIBLE VALUE	FOR ALL VALUES
&EXCLUD_SEEDED	Non-seeded values only (i.e. ‘Y’ to exclude)	NULL

 

DB Access

OWNER	OBJECT NAME	OBJECT TYPE	PRIVILEGE
AR	RA_TERMS_TL	TABLE	SELECT
AR	RA_TERMS_B	TABLE	SELECT
AR	AR_CONS_BILL_CYCLES_TL	TABLE	SELECT
AR	RA_TERMS_LINES	TABLE	SELECT
APPLSYS	FND_LOOKUP_VALUES	TABLE	SELECT

 

Piyush Ohri

 

“He who is too busy doing good finds no time to be good.” – Rabindranath Tagore

Order Management - Payment Types

Oracle Release

12.1.3

 

Setup : Orders : Payment Types

 

SELECT HAOU.NAME "Operating Unit",

       OPTT.NAME "Payment Type Name",

       OPTT.DESCRIPTION "Description",

       FLV.MEANING "Payment Type Code",

       ARM.NAME "Receipt Method",

       TO_DATE(OPTA.START_DATE_ACTIVE, 'DD-MON-RRRR') "Start Date",

       TO_DATE(OPTA.END_DATE_ACTIVE, 'DD-MON-RRRR') "End Date",

       OPTA.ENABLED_FLAG "Enabled",

       OPTA.DEFER_PAYMENT_PROCESSING_FLAG "Defer",

       OPTA.CREDIT_CHECK_FLAG "Credit Check"

  FROM APPS.OE_PAYMENT_TYPES_ALL      OPTA,

       APPS.OE_PAYMENT_TYPES_TL       OPTT,

       APPS.FND_LOOKUP_VALUES         FLV,

       APPS.AR_RECEIPT_METHODS        ARM,

       APPS.HR_ALL_ORGANIZATION_UNITS HAOU

 WHERE 1 = 1

   AND OPTA.PAYMENT_TYPE_CODE = OPTT.PAYMENT_TYPE_CODE

   AND OPTA.ORG_ID = OPTT.ORG_ID

   AND OPTT.LANGUAGE = OPTT.SOURCE_LANG

   AND OPTA.PAYMENT_TYPE_CODE = FLV.LOOKUP_CODE

   AND FLV.LOOKUP_TYPE = 'OE_PAYMENT_TYPE'

   AND FLV.LANGUAGE = FLV.SOURCE_LANG

   AND OPTA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID(+)

   AND OPTA.ORG_ID = HAOU.ORGANIZATION_ID

   AND OPTA.LAST_UPDATED_BY NOT IN DECODE(NVL(&EXCLUD_SEEDED, 'N'), 'Y', 1, -999)

   AND HAOU.NAME IN

       (SELECT DISTINCT NVL(PARAM_LIST, NAME)

          FROM (SELECT TRIM(REGEXP_SUBSTR(PARAMETER, '[^,]+', 1, LEVEL)) PARAM_LIST, NULL

                  FROM (SELECT TRIM(DECODE(&OPERATING_UNITS, 'ALL', NULL, &OPERATING_UNITS)) PARAMETER

                          FROM DUAL) T

                CONNECT BY REGEXP_SUBSTR(PARAMETER, '[^,]+', 1, LEVEL) IS NOT NULL) PARAM_TBL,

               (SELECT NULL, NAME FROM APPS.HR_ALL_ORGANIZATION_UNITS) BASE_TBL)

 ORDER BY HAOU.NAME, OPTT.NAME

 

Filters

NAME	POSSIBLE VALUE	FOR ALL VALUES
&EXCLUD_SEEDED	Non-seeded values only (i.e. ‘Y’ to exclude)	NULL
& OPERATING_UNITS	Comma separated list, enclosed in single quotes for e.g. ‘OU1, OU2’	NULL

 

DB Access

OWNER	OBJECT NAME	OBJECT TYPE	PRIVILEGE
ONT	OE_PAYMENT_TYPES_ALL	TABLE	SELECT
ONT	OE_PAYMENT_TYPES_TL	TABLE	SELECT
APPLSYS	FND_LOOKUP_VALUES	TABLE	SELECT
AR	AR_RECEIPT_METHODS	TABLE	SELECT
HR	HR_ALL_ORGANIZATION_UNITS	TABLE	SELECT

 

Piyush Ohri

 

“You can’t get much done in life if you only work on the days when you feel good.” – Jerry West

Order Management - System Parameters

Oracle Release

12.1.3

 

Setup : System Parameters

 

SELECT HAOU.NAME "Operating Unit"

       ,OSPDT.NAME "Parameter"

       ,OSPA.PARAMETER_VALUE "Value"

FROM   APPS.OE_SYS_PARAMETERS_ALL   OSPA

      ,APPS.OE_SYS_PARAMETER_DEF_TL OSPDT

      ,APPS.HR_ALL_ORGANIZATION_UNITS HAOU

WHERE  1 = 1

AND    OSPA.PARAMETER_CODE = OSPDT.PARAMETER_CODE

AND    OSPDT.LANGUAGE = 'US'

AND    HAOU.ORGANIZATION_ID (+) = OSPA.ORG_ID

AND    HAOU.NAME IN (

SELECT DISTINCT NVL(PARAM_LIST, NAME)

  FROM (SELECT TRIM(REGEXP_SUBSTR(PARAMETER, '[^,]+', 1, LEVEL)) PARAM_LIST, NULL

          FROM (SELECT TRIM(DECODE(:OPERATING_UNITS, 'ALL', NULL,:OPERATING_UNITS) )PARAMETER FROM DUAL) T

        CONNECT BY REGEXP_SUBSTR(PARAMETER, '[^,]+', 1, LEVEL) IS NOT NULL) PARAM_TBL,

       (SELECT NULL, NAME FROM APPS.HR_ALL_ORGANIZATION_UNITS) BASE_TBL)

ORDER  BY HAOU.NAME

         ,OSPDT.NAME

 

Filters

NAME	POSSIBLE VALUE	FOR ALL VALUES
: OPERATING_UNITS	Comma separated list, enclosed in single quotes for e.g. ‘OU1, OU2’	NULL

 

DB Access

OWNER	OBJECT NAME	OBJECT TYPE	PRIVILEGE
ONT	OE_SYS_PARAMETERS_ALL	TABLE	SELECT
ONT	OE_SYS_PARAMETER_DEF_TL	TABLE	SELECT
HR	HR_ALL_ORGANIZATION_UNITS	TABLE	SELECT

 

Enhancement

Please help answer the two questions below –

·         Did you use this SQL in anyone of the listed EBS releases, if not mention the release you tried it on?

·         Was any information missing or incorrect from that available on front-end of your release? Please specify release # and missing information details.

 

Piyush Ohri

 

“Clouds come floating into my life, no longer to carry rain or usher storm, but to add color to my sunset sky.” - Rabindranath Tagore