Fixed Asset ERP Data to Blackline Tool

 Select  '1' as KEY,

        market,

       segment4,

       segment1,

       segment2,

       Period_End_Date,

       sum(period_activity)+ sum(begin_balance) BALANCE

from 

(

with bal as (select /*+ materialize */ * FROM  (SELECT 

FTH.book_type_code book_type_code,

fab.asset_number Cloud_Asset_Number,

gcc.segment1 , 

gcc.segment2 , 

gcc.segment3 , 

'COST' Type,

fab.asset_id,

FDD.cost,

FDP.PERIOD_NAME PERIOD_NAME,

fadh.units_assigned

FROM 

fa_distribution_history fadh, 

fa_transaction_headers fth,

gl_code_combinations gcc, 

fa_additions_b fab,

FA_DEPRN_PERIODS FDP,

FA_DEPRN_DETAIL FDD

WHERE 1=1

and fth.asset_id = fadh.asset_id

and fth.book_type_code = fadh.book_type_code

and fadh.code_combination_id = gcc.code_combination_id

and fab.asset_id = fth.asset_id

and fadh.transaction_header_id_in = fth.transaction_header_id

and fadh.book_type_code = fth.book_type_code

AND FDD.ASSET_ID = FTH.ASSET_ID

AND FDD.DISTRIBUTION_ID = FADH.DISTRIBUTION_ID

AND FDD.PERIOD_COUNTER = (SELECT MAX(FDP1.PERIOD_COUNTER)

FROM FA_DEPRN_PERIODS FDP1

,FA_DEPRN_DETAIL FDS1

WHERE FDS1.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE

AND FDS1.ASSET_ID = FDD.ASSET_ID

AND FDS1.DISTRIBUTION_ID = FDD.DISTRIBUTION_ID

AND FDP1.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE

AND FDS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER

AND FDP1.CALENDAR_PERIOD_OPEN_DATE <= fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY')

)

AND FDD.PERIOD_COUNTER = FDP.PERIOD_COUNTER

AND FDD.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE

UNION ALL

SELECT 

FADH.book_type_code book_type_code,

fab.asset_number Cloud_Asset_Number,

gcc.segment1 , 

gcc.segment2 , 

gcc.segment3 , 

'DEP_RES' Type,

fab.asset_id,

fds.deprn_reserve COST,

FDP.PERIOD_NAME PERIOD_NAME,

fadh.units_assigned

FROM 

FA_DEPRN_DETAIL fds, 

fa_distribution_history fadh, 

gl_code_combinations gcc, 

fa_additions_b fab, 

FA_DEPRN_PERIODS FDP

WHERE 1=1

and fadh.asset_id = fds.asset_id

and fadh.DISTRIBUTION_ID = fds.DISTRIBUTION_ID

and fadh.book_type_code = fds.book_type_code

and fadh.code_combination_id = gcc.code_combination_id

and fab.asset_id = fadh.asset_id

AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER

AND FDP.PERIOD_COUNTER = (SELECT MAX(FDP1.PERIOD_COUNTER)

FROM FA_DEPRN_PERIODS FDP1

,FA_DEPRN_DETAIL FDS1

WHERE FDS1.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE

AND FDS1.ASSET_ID = FDS.ASSET_ID

AND FDS1.DISTRIBUTION_ID = FDS.DISTRIBUTION_ID

AND FDP1.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE

AND FDS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER

AND FDP1.CALENDAR_PERIOD_OPEN_DATE <= fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY')

) 

AND FDS.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE))

,CURRENT_UNITS AS (select /*+ materialize */ * FROM  (SELECT FAH.ASSET_ID , FAH.UNITS , FAH.BOOK_TYPE_CODE , GCC1.SEGMENT4 COST_SEGMENT4, GCC2.SEGMENT4 RESERVE_SEGMENT4

FROM 

FA_ASSET_HISTORY FAH

,FA_CATEGORY_BOOKS FCB

,GL_CODE_COMBINATIONS GCC1

,GL_CODE_COMBINATIONS GCC2

,(SELECT MAX(FTH.TRANSACTION_HEADER_ID) TRANSACTION_HEADER_ID, FTH.ASSET_ID , FTH.BOOK_TYPE_CODE

FROM  FA_TRANSACTION_HEADERS FTH

,XLA_EVENTS XE

,FA_ASSET_HISTORY FAH1

WHERE 1=1

AND FTH.EVENT_ID = XE.EVENT_ID

AND XE.EVENT_DATE  <= LAST_DAY(fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY'))

AND FTH.TRANSACTION_HEADER_ID = FAH1.TRANSACTION_HEADER_ID_IN

AND FTH.BOOK_TYPE_CODE = FAH1.BOOK_TYPE_CODE

AND FTH.ASSET_ID = FAH1.ASSET_ID

GROUP BY FTH.ASSET_ID , FTH.BOOK_TYPE_CODE) ASSET_HISTORY

WHERE 1=1

AND ASSET_HISTORY.ASSET_ID = FAH.ASSET_ID

AND ASSET_HISTORY.TRANSACTION_HEADER_ID = FAH.TRANSACTION_HEADER_ID_IN

AND ASSET_HISTORY.BOOK_TYPE_CODE = FAH.BOOK_TYPE_CODE

AND FAH.CATEGORY_ID = FCB.CATEGORY_ID

AND FAH.BOOK_TYPE_CODE = FCB.BOOK_TYPE_CODE

AND FCB.ASSET_COST_ACCOUNT_CCID = GCC1.CODE_COMBINATION_ID

AND FCB.RESERVE_ACCOUNT_CCID = GCC2.CODE_COMBINATION_ID))

SELECT rf.ancestor_pk1_value           

                        || Chr(9)            market,

                         (CASE WHEN Type = 'DEP_RES' THEN CU.RESERVE_SEGMENT4

ELSE CU.COST_SEGMENT4 END)

                        || Chr(9)            segment4,

                        bal.segment1

                        || Chr(9)            segment1,

                        bal.segment2

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)            segment2,

To_char(Last_day(fnd_date.String_to_date(gp.period_name,

                 'Mon-YY')),

'MM/DD/YYYY'

|| Chr(9)

|| Chr(9)

|| Chr(9))           Period_End_Date,

(CASE WHEN Type = 'DEP_RES' THEN To_char((round(bal.COST,2))*(-1),'fm999999999990.00')

ELSE  To_char(round(bal.cost,2),'fm999999999990.00') END) period_activity ,

TO_CHAR(nvl(null,0) ,'fm999999999990.00') begin_balance

from 

bal,

CURRENT_UNITS CU,

gl_ledgers GLG,

(SELECT DISTINCT tree_code,ancestor_pk1_value,distance,pk1_value FROM gl_seg_val_hier_rf) rf,

gl_periods GP,

fnd_lookup_values  flv,

fa_book_controls fbc

where 1=1

and GLG.ledger_category_code = 'PRIMARY'

AND gp.period_num = :P_PERIOD_NUM

AND gp.period_year = :P_PERIOD_YEAR

AND GLG.name = nvl(:P_LEDGER,GLG.name)

AND fnd_date.String_to_date(bal.period_name,'Mon-YY') <= fnd_date.String_to_date(gp.period_name,'Mon-YY')

AND fbc.book_type_code = bal.book_type_code

and fnd_date.String_to_date(bal.period_name,'Mon-YY') <=NVL(flv.end_Date_active,SYSDATE+1)

AND rf.pk1_value = bal.segment1

AND rf.tree_code = flv.description

AND flv.lookup_type = 'xxxxxxxxxxxx'

AND rf.distance = flv.tag

and flv.language='US'

and flv.lookup_code = 'xxxxxxxxxxxx' -- RICE ID for the given object

AND fbc.book_class = 'CORPORATE'

AND GLG.LEDGER_ID = fbc.set_of_books_id

AND CU.ASSET_ID = BAL.ASSET_ID

AND CU.BOOK_TYPE_CODE = BAL.BOOK_TYPE_CODE

)

group by 

market,

       segment4,

       segment1,

       segment2,

       Period_End_Date

order by 

market,

       segment4,

       segment1,

       segment2

Oracle Job Requisition and Pre employment Query and Tables

 with REQ_EFF as (SELECT  jobfamilytranslationpeo.job_family_name Requisition_type

       ,irv.RECRUITING_TYPE_CODE

       ,irv.REQUISITION_NUMBER

   ,(SELECT pjt.name

     FROM per_jobs_f pj

      ,per_jobs_f_tl pjt

WHERE pj.JOB_ID = irv.job_id

AND pj.job_id = pjt.job_id

AND sysdate between pjt.EFFECTIVE_START_DATE and pjt.EFFECTIVE_END_DATE

AND sysdate between pj.EFFECTIVE_START_DATE and pj.EFFECTIVE_END_DATE

AND pjt.LANGUAGE = USERENV('LANG')

    ) job_name

,ppnf.full_name cand_name

,phn.phone_number cand_phone_number

,email.email_address cand_email

,per.date_of_birth

,irv.open_date date_needed_by

,irv.attribute_char8 client

,irv.attribute_char7 project

,(hgeo.geography_element3 ||','|| hgeo.geography_element2 ||','|| hgeo.geography_element1) primary_location

,plt.location_name primary_work_location

-- ,(case when houft.organization_id = irv.LEGAL_EMPLOYER_ID then houft.name else null)

,ple.name legal_employer

,houft.name department_name

,fabu.bu_name business_unit

,DECODE(irv.WORKER_TYPE_CODE,'E','Employee','Contingent Worker') WORKER_TYPE_CODE

,irv.ATTRIBUTE_CHAR10 Worker_Category

,sub.SUBMISSION_ID

,(select PEI_INFORMATION15

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='LOGISTICS' ) logistics_owned_by

,(select PEI_INFORMATION_DATE3

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='XX_LOGISTICS' ) logistics_completion_date

  ,(select PEI_INFORMATION_DATE2

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='Craft' )craft_mobilisation_date

  ,(select PEI_INFORMATION20

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='Screening' )owned_by

  ,(select PEI_INFORMATION_DATE15

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='xxx' )xx_raft_completion_date

  ,(select PEI_INFORMATION8

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='Medical_Health' 

  AND PEI_INFORMATION1='Health (Internal)')medical_health

  ,(select PEI_INFORMATION_DATE3

          from irc_ja_extra_info

          where SUBMISSION_ID=sub.SUBMISSION_ID

          AND PEI_INFORMATION_CATEGORY ='Medical_Health' 

  AND PEI_INFORMATION1='Health (Internal)')medical_completion_date

FROM irc_requisitions_vl irv

    ,irc_states_vl st

,irc_phases_vl ph

    ,irc_submissions sub

    ,irc_candidates cand

,per_person_names_f ppnf

,per_phones phn

,per_email_addresses email

,per_persons per

,irc_geo_hier_nodes igeo

    ,hz_geographies hgeo

,per_locations pl

,per_location_details_f plf

,per_location_details_f_tl plt

,per_job_family_f jobfamilypeo 

    ,per_job_family_f_tl jobfamilytranslationpeo

,hr_organization_units_f_tl houft

,fun_all_business_units_v fabu

-- ,irc_ja_extra_info ijei

,per_legal_employers ple

WHERE 1=1

--AND irv.requisition_number = '297' 

AND irv.requisition_id = sub.requisition_id

AND ph.phase_id = sub.current_phase_id

AND st.state_id = sub.current_state_id

AND sub.active_flag = 'Y'

AND cand.person_id = sub.person_id

AND ppnf.person_id = cand.person_id

AND ppnf.name_type = 'GLOBAL'

--AND cand.person_id in (300000147313216)

AND phn.phone_id(+) = cand.cand_phone_id

AND email.email_address_id(+) = cand.cand_email_id

AND per.person_id= cand.person_id

AND igeo.GEOGRAPHY_ID = hgeo.GEOGRAPHY_ID

AND irv.GEOGRAPHY_NODE_ID = igeo.GEOGRAPHY_NODE_ID

AND pl.location_id = irv.location_id

AND pl.location_id = plf.location_id

AND plt.LOCATION_DETAILS_ID = plf.LOCATION_DETAILS_ID

AND plt.LANGUAGE = USERENV('LANG')

AND JobFamilyPEO.JOB_FAMILY_ID = JobFamilyTranslationPEO.JOB_FAMILY_ID

AND JobFamilyTranslationPEO.LANGUAGE = USERENV('LANG')

AND SYSDATE BETWEEN JobFamilyPEO.EFFECTIVE_START_DATE AND JobFamilyPEO.EFFECTIVE_END_DATE

AND SYSDATE BETWEEN JobFamilyTranslationPEO.EFFECTIVE_START_DATE AND JobFamilyTranslationPEO.EFFECTIVE_END_DATE

AND JobFamilyPEO.JOB_FAMILY_ID = irv.JOB_FAMILY_ID

AND houft.organization_id = irv.DEPARTMENT_ID

AND fabu.BU_ID = irv.BUSINESS_UNIT_ID

AND houft.LANGUAGE = USERENV('LANG')

AND ple.organization_id =irv.LEGAL_EMPLOYER_ID

AND ple.STATUS ='A'

AND (jobfamilytranslationpeo.job_family_name IN (:p_requisition_type) OR COALESCE(:p_requisition_type, NULL) IS NULL)

AND (irv.requisition_id IN (:p_requisition_number) OR COALESCE(:p_requisition_number, NULL) IS NULL)

AND (ppnf.full_name IN (:p_candidate_name) OR COALESCE(:p_candidate_name, NULL) IS NULL)

AND (irv.open_date IN (:p_date_need_by) OR COALESCE(:p_date_need_by, NULL) IS NULL)

AND (fabu.BU_ID IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)

AND (houft.organization_id IN (:p_department_id) OR COALESCE(:p_department_id, NULL) IS NULL)

AND (plt.location_name IN (:p_primary_location) OR COALESCE(:p_primary_location, NULL) IS NULL)

AND (ple.organization_id IN (:p_legal_employer) OR COALESCE(:p_legal_employer, NULL) IS NULL)

order by ppnf.full_name

)

SELECT Requisition_type

,RECRUITING_TYPE_CODE

,REQUISITION_NUMBER

,job_name

,cand_name

,cand_phone_number

,cand_email

,date_of_birth

,date_needed_by

,client

,project

,primary_location

,primary_work_location

,legal_employer

,department_name

,business_unit

,WORKER_TYPE_CODE

,Worker_Category

,SUBMISSION_ID

,logistics_owned_by

,logistics_completion_date

,screening_trade_and_craft_mobilisation_date

,screening_trade_and_craft_owned_by

,screening_trade_and_craft_completion_date

,medical_occupational_health_owned_by

,medical_occupational_health_completion_date

FROM REQ_EFF

WHERE 1=1

AND (logistics_completion_date IN (:p_logistics_completion_date) OR COALESCE(:p_logistics_completion_date, NULL) IS NULL)

AND (screening_trade_and_craft_completion_date IN (:p_screening_trade_and_craft_completiond_date) OR COALESCE(:p_screening_trade_and_craft_completiond_date, NULL) IS NULL)

AND (medical_occupational_health_completion_date IN (:p_medical_occupational_health_completion_date) OR COALESCE(:p_medical_occupational_health_completion_date, NULL) IS NULL)

Query for Credit Hold Report Oracle Erp Cloud

 SELECT DISTINCT DOFL.SOURCE_ORDER_NUMBER

                ,DOFL.SOURCE_LINE_NUMBER 

, DHCT.HOLD_DESCRIPTION

            ,DHCB.hold_code

,DHCT.HOLD_NAME

,DHA.ORDER_NUMBER

,EGT.DESCRIPTION ITEM_DESC

,EGB.ITEM_NUMBER    ITEM

,HP.PARTY_NAME 

,HCA.ATTRIBUTE1

,DHI.APPLY_DATE

,NVL(PPNF.FULL_NAME,PU.USERNAME) HOLD_CREATED_BY

, hou.name       OU

, hca.account_number

,DHI.HOLD_COMMENTS

, (SELECT HCP.CREDIT_HOLD

   FROM   HZ_CUSTOMER_PROFILES_F HCP

   WHERE  HCP.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

   AND ROWNUM = 1

   ) ACC_CREDIT_HOLD

from DOO_HOLD_CODES_TL      DHCT 

   , DOO_HOLD_CODES_B       DHCB

   , DOO_HOLD_INSTANCES     DHI

   , DOO_ORDER_FLINES_V     DOFL

   , DOO_HEADERS_ALL        DHA

   , egp_system_items_b     egb

   , egp_system_items_TL egt

   , HZ_PARTIES             HP

   , HZ_CUST_ACCOUNTS       HCA

   , PER_USERS              PU

   , PER_PERSON_NAMES_F     PPNF

   , HR_OPERATING_UNITS     hou

WHERE 1=1

AND DHCT.LANGUAGE                 = USERENV('LANG')

AND DHCB.HOLD_CODE_ID             = DHCT.HOLD_CODE_ID

AND DHCB.HOLD_CODE_ID             = DHI.HOLD_CODE_ID

AND DHI.TRANSACTION_ENTITY_NAME1  = 'DOO_ORDER_FLINES_V'

AND DHI.TRANSACTION_ENTITY_ID1  = DOFL.FULFILL_LINE_ID   

AND DOFL.HEADER_ID                = DHA.HEADER_ID

AND DOFL.INVENTORY_ITEM_ID        = EGB.INVENTORY_ITEM_ID

AND DOFL.INVENTORY_ORGANIZATION_ID = EGB.ORGANIZATION_ID

AND EGB.INVENTORY_ITEM_ID         = EGT.INVENTORY_ITEM_ID

AND  EGB.ORGANIZATION_ID           = EGT.ORGANIZATION_ID

AND  DHA.SOLD_TO_PARTY_ID          = HP.PARTY_ID

AND  HP.PARTY_ID                   = HCA.PARTY_ID

AND  DHI.RELEASE_DATE IS  NULL

AND HCA.ACCOUNT_TERMINATION_DATE >= TRUNC(SYSDATE)

AND  DHI.CREATED_BY                = PU.USERNAME

AND PU.PERSON_ID                   = PPNF.PERSON_ID(+)

AND PPNF.NAME_TYPE  (+)            ='GLOBAL'

AND dha.org_id                    = hou.organization_id

AND hou.name                      = :P_Business_Unit

AND (hou.name IN (:p_bu) OR COALESCE (:p_bu,NULL) IS NULL)

UNION ALL

SELECT DISTINCT DHA.SOURCE_ORDER_NUMBER

                ,DLA.SOURCE_LINE_NUMBER 

, DHCT.HOLD_DESCRIPTION

            ,DHCB.hold_code

,DHCT.HOLD_NAME

,DHA.ORDER_NUMBER

,EGT.DESCRIPTION ITEM_DESC

,EGB.ITEM_NUMBER    ITEM

,HP.PARTY_NAME 

,HCA.ATTRIBUTE1

,DHI.APPLY_DATE

,NVL(PPNF.FULL_NAME,PU.USERNAME) HOLD_CREATED_BY

, hou.name       OU

, hca.account_number

,DHI.HOLD_COMMENTS

, (SELECT HCP.CREDIT_HOLD

   FROM   HZ_CUSTOMER_PROFILES_F HCP

   WHERE  HCP.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID

   AND ROWNUM = 1

   ) ACC_CREDIT_HOLD

from DOO_HOLD_CODES_TL      DHCT 

   , DOO_HOLD_CODES_B       DHCB

   , DOO_HOLD_INSTANCES     DHI

   , DOO_HEADERS_ALL        DHA

   , DOO_LINES_ALL          DLA

   , egp_system_items_b     egb

   , egp_system_items_TL egt

   , HZ_PARTIES             HP

   , HZ_CUST_ACCOUNTS       HCA

   , PER_USERS              PU

   , PER_PERSON_NAMES_F     PPNF

   , HR_OPERATING_UNITS     hou

WHERE 1=1

AND DHCT.LANGUAGE                 = USERENV('LANG')

AND DHCB.HOLD_CODE_ID             = DHCT.HOLD_CODE_ID

AND DHCB.HOLD_CODE_ID             = DHI.HOLD_CODE_ID

AND DHI.TRANSACTION_ENTITY_NAME1  = 'DOO_ORDER_HEADERS_V'

AND DHI.TRANSACTION_ENTITY_ID1  = DHA.HEADER_ID   

AND DHA.HEADER_ID                  =  DLA.HEADER_ID

AND DLA.INVENTORY_ITEM_ID          = EGB.INVENTORY_ITEM_ID

AND DLA.INVENTORY_ORGANIZATION_ID  = EGB.ORGANIZATION_ID

AND EGB.INVENTORY_ITEM_ID          = EGT.INVENTORY_ITEM_ID

AND  EGB.ORGANIZATION_ID           = EGT.ORGANIZATION_ID

AND  DHA.SOLD_TO_PARTY_ID          = HP.PARTY_ID

AND  HP.PARTY_ID                   = HCA.PARTY_ID

AND  DHI.RELEASE_DATE IS  NULL

AND  DHI.CREATED_BY                = PU.USERNAME

AND PU.PERSON_ID                   = PPNF.PERSON_ID(+)

AND PPNF.NAME_TYPE  (+)            ='GLOBAL'

AND dha.org_id                    = hou.organization_id

AND HCA.ACCOUNT_TERMINATION_DATE >= TRUNC(SYSDATE)

AND (hou.name IN (:p_bu) OR COALESCE (:p_bu,NULL) IS NULL)

ORDER BY 1,6

Changing the Purchase Order Layout in Oracle Fusion Applications

Often times there is a requirement to change the Purchase Order PDF report during implementation to add a company's logo or branding, or add or remove information from the layout itself.

Below are the steps for you to modify a Purchase Order Report:

1. Upload a custom Purchase Order Template to BI Publisher. (see the link for the steps)
2. Navigate to Setup and Maintenance
3. Click on the Task Pane then click on Search

For More Information Please follow below link.

 https://lifeofanoracleprodigy.blogspot.com/2018/09/changing-purchase-order-layout.html

Query for Supplier Payment Comparison of that Period(Vendor Spend Query)

 SELECT POS.SEGMENT1

       , HP.PARTY_NAME

  ,cur_period.PAYMENT_AMOUNT  cur_pay_amt

  ,prev_period.PAYMENT_AMOUNT  prev_pay_amt

  ,ROUND((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100),1)||'%' increase_decrease

FROM

        (SELECT    AIA.VENDOR_ID              VENDOR_ID

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name' 

AND gl.PERIOD_YEAR = :P_Fiscal_Year

AND gl.PERIOD_NAME = :P_Period

--AND   SYSDATE BETWEEN gl.start_date and gl.end_date

GROUP BY AIA.VENDOR_ID

        ) cur_period,

        (SELECT     AIA.VENDOR_ID     VENDOR_ID  

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name'

AND gl.period_name = (

                      SELECT  gl.period_name

                              FROM  

                               gl_periods      gl,

                       gl_periods      gl2

                              WHERE 1=1

                      AND gl.period_set_name ='XX Period Name'

                      AND (

       ( 

           gl2.period_name = :P_Comp_Period

  AND gl2.PERIOD_YEAR = :P_Comp_Fis_Year

  AND trunc(gl2.start_date) between gl.start_date and gl.end_date

    )

  OR

  (

  ( trunc(gl2.start_date-1) between gl.start_date and gl.end_date ) 

   AND gl2.period_name = :P_Period

   AND gl2.PERIOD_YEAR = :P_Fiscal_Year

   AND :P_Comp_Period IS NULL

   AND :P_Comp_Fis_Year IS NULL

  )

  )

                      AND gl2.period_set_name ='XX Period Name'

  )   

        GROUP BY AIA.VENDOR_ID

        ) prev_period

        ,POZ_SUPPLIERS POS

        ,HZ_PARTIES     HP

WHERE POS.VENDOR_ID = cur_period.vendor_id(+)

AND   POS.VENDOR_ID  = Prev_period.vendor_id(+)

AND   POS.party_id  = hp.party_id

AND ((COALESCE(NULL, :P_Supplier) IS NULL)

       OR (HP.PARTY_NAME IN (:P_Supplier)))

and (NVL(cur_period.PAYMENT_AMOUNT ,0)<>0 OR NVl(prev_period.PAYMENT_AMOUNT ,0)<>0)

AND abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)) > = NVL(:P_Change,abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)))

link between PO and Projects Query in oracle fusion (po_headers_all and pjf_projects_all_b)

 SELECT

ph.segment1 CommitmentNumber
,pl.line_num ItemNumber
,(SELECT DISTINCT esi.item_number
  FROM egp_system_items_b esi
  WHERE esi.inventory_item_id=pl.item_id) ITEM
,pl.item_description ItemDescription
,plt.line_type ITEM_TYPE
,gcc.segment1||gcc.segment2||gcc.segment3 ACCT_UNIT
,gcc.segment4 ACCT_CATEGORY
,ppa.segment1 ActivityNumber
,pl.list_price ENT_UNIT_CST
,pll.quantity QUANTITY
,(pll.quantity*pl.list_price) CommitmentItemAmount
,pll.need_by_date POSTED_DATE
,ps.segment1 CompanyNumber
,hp.party_name Description
,ppa.attribute3 ProjectIdentifier
,ph.creation_date CommitmentDate
,ppn.full_name BUYER_CODE
,pld.location_code REQ_LOCATION
,pl.attribute1||','||pl.attribute2 BudgetLineItem
,'Purchase Order' CommitmentType
FROM po_headers_all ph,po_lines_all pl
,po_line_types_tl plt
,gl_code_combinations gcc
,po_distributions_all pd
,pjf_projects_all_b ppa
,po_line_locations_all pll
,poz_suppliers ps
,hz_parties hp
,per_person_names_f ppn
,per_location_details_f_tl pld
,per_location_details_f pldf
WHERE ph.po_header_id=pl.po_header_id
AND plt.line_type_id=pl.line_type_id
AND plt.language=USERENV('LANG')
AND pd.po_header_id=pl.po_header_id
AND pd.po_line_id=pl.po_line_id
AND pd.code_combination_id=gcc.code_combination_id
AND gcc.enabled_flag='Y'
AND SYSDATE BETWEEN NVL(gcc.start_date_active,SYSDATE) AND NVL(gcc.end_date_active,SYSDATE)
AND pd.Pjc_Project_id=ppa.project_id
AND pll.po_header_id=pl.po_header_id
AND pll.po_line_id=pl.po_line_id
AND ph.vendor_id=ps.vendor_id
AND ps.party_id=hp.party_id
AND ppn.person_id=ph.agent_id
AND ppn.name_type='GLOBAL'
AND SYSDATE BETWEEN NVL(ppn.effective_start_date,SYSDATE) AND NVL(ppn.effective_end_date,SYSDATE)
AND ps.enabled_flag='Y'
AND SYSDATE BETWEEN NVL(ps.start_date_active,SYSDATE) AND NVL(ps.end_date_active,SYSDATE)
AND pldf.location_id=pd.Deliver_to_location_ID
AND pldf.active_status='A'
AND SYSDATE BETWEEN NVL(pldf.effective_start_date,SYSDATE) AND NVL(pldf.effective_end_date,SYSDATE)
AND pld.location_details_id=pldf.location_details_id
AND pld.language=USERENV('LANG')

Generate Certificate from Oracle Fusion Erp Cloud

 Step 1: click Instance url

https://<Instance url>/FndManageImportExportFilesService?wsdl

Step 2: 

You'll see certificate data in this tag : dsig:X509Certificate

Step 3: 

Your certificate should be in this format : 

-----BEGIN CERTIFICATE-----

Copy data from Step 2

-----END CERTIFICATE-----

open a notepad and copy those header and footer and insert data in between

Step 4 :

Save the file as <Filename.cer>

Query to get DFF and Segment Values

 SELECT ffv.descriptive_flexfield_name “DFF Name”,

ffv.application_table_name “Table Name”,
ffv.title “Title”,
ap.application_name “Application”,
ffc.descriptive_flex_context_code “Context Code”,
ffc.descriptive_flex_context_name “Context Name”,
ffc.description “Context Desc”,
ffc.enabled_flag “Context Enable Flag”,
att.column_seq_num “Segment Number”,
att.form_left_prompt “Segment Name”,
att.application_column_name “Column”,
fvs.flex_value_set_name “Value Set”,
att.display_flag “Displayed”,
att.enabled_flag “Enabled”,
att.required_flag “Required”

FROM apps.fnd_descriptive_flexs_vl ffv,
apps.fnd_descr_flex_contexts_vl ffc,
apps.fnd_descr_flex_col_usage_vl att,
apps.fnd_flex_value_sets fvs,
apps.fnd_application_vl ap

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id=ffv.application_id
AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id=att.flex_value_set_id
AND ffv.title like ‘Give Title Name’
AND ffc.descriptive_flex_context_code like ‘Give Context Code Value’

ORDER BY att.column_seq_num

Enterprise Structure Query in Oracle Fusion Cloud

SELECT   DISTINCT

        hroutl_bg.NAME enterprise,

        hroutl_bg.organization_id bu_id,

       lep.legal_entity_id,

       lep.NAME legal_entity,

       hroutl_ou.NAME bu_name,

       hroutl_ou.organization_id org_id,

      glev.FLEX_SEGMENT_VALUE

  FROM xle_entity_profiles lep,

       xle_registrations reg,

      hz_parties hzp,

       hr_operating_units hro,

       hr_all_organization_units_tl hroutl_bg,

       hr_all_organization_units_tl hroutl_ou,

       hr_organization_units gloperatingunitseo,

      gl_legal_entities_bsvs glev

WHERE lep.transacting_entity_flag = 'Y'

   AND lep.party_id = hzp.party_id

   AND lep.legal_entity_id = reg.source_id

   AND reg.source_table = 'XLE_ENTITY_PROFILES'

  AND reg.identifying_flag = 'Y'

   AND lep.legal_entity_id = hro.default_legal_context_id

   AND gloperatingunitseo.organization_id = hro.organization_id

   AND hroutl_bg.organization_id = hro.business_group_id

   AND hroutl_bg.language ='US'

   AND hroutl_ou.organization_id = hro.organization_id

   AND glev.legal_entity_id = lep.legal_entity_id;

Oracle Fusion Cloud - Customer Interface Status Error Codes

CUSTOMER INTERFACE STATUS ERROR CODES


Problem Description
-------------------

You are running Customer Interface RACUST and there is an error message in the
INTERFACE_STATUS column, but you don't know what it means.


Solution Description
--------------------

INTERFACE_STATUS appears in the following tables:
  RA_CUSTOMERS_INTERFACE_ALL
  RA_CUSTOMER_PROFILES_INT_ALL
  RA_CONTACT_PHONES_INT_ALL
  RA_CUSTOMER_BANKS_INT_ALL
  RA_CUST_PAY_METHOD_INTERFACE

These are the message codes and their meaning:

A1 --> The customer reference for update does not exist in RA_CUSTOMERS

A2 --> The address reference for update does not exist in RA_ADDRESSES

A3 --> Customer reference for insert is already defined in RA_CUSTOMERS

A4 --> Site use for this address reference already exists in the database

A5 --> Customer Number already assigned to a different customer

B1 --> ORIG_SYSTEM_ADDRESS_REF is mandatory when specifying an address

B2 --> ADDRESS1 is mandatory when specifying an address

B3 --> COUNTRY is mandatory when specifying an address

B4 --> SITE_USE_CODE is mandatory when inserting an address

B5 --> PRIMARY_SITE_USE_FLAG is mandatory when inserting an address

B6 --> CUSTOMER_CLASS_CODE is not defined in AR_LOOKUPS

B7 --> CUSTOMER_PROFILE_CLASS_NAME has an invalid value

B8 --> STATE is not defined in AR_LOCATION_VALUES

B9 --> COUNTRY is not defined in fnd_territories

B0 --> SITE_USE_CODE is not defined in AR_LOOKUPS

C1 --> This customer reference has two different customer names defined

C2 --> This customer reference has two different customer numbers defined

C3 --> This customer reference has two different parent customer references

C5 --> Customer reference has two different customer class codes defined

C6 --> This customer reference has two identical primary site uses defined

D1 --> Address reference has two different ADDRESS1 values

D2 --> Address reference has two different ADDRESS2 values

D3 --> Address reference has two different ADDRESS3 values

D4 --> Address reference has two different ADDRESS4 values

D5 --> Address reference has two different cities

D6 --> Address reference has two different postal codes

D7 --> Address reference has two different states

D8 --> Address reference has two different provinces

D9 --> Address reference has two different counties

D0 --> Address reference has two different countries

E1 --> Address reference has two identical site use codes

E2 --> Address reference has two different customers

F1 --> ORIG_SYSTEM_TELEPHONE_REF mandatory for telephone information

F2 --> TELEPHONE is mandatory when specifying telephone information

F3 --> TELEPHONE_TYPE is mandatory when specifying telephone information

F4 --> TELEPHONE_TYPE is not defined in AR_LOOKUPS

F5 --> Telephone reference for insert is already defined in RA_PHONES

F6 --> Telephone reference for update does not exist in RA_PHONES

G1 --> ORIG_SYSTEM_CONTACT_REF mandatory for contact information

G2 --> LAST_NAME is mandatory when specifying a contact

G3 --> CONTACT_TITLE is not defined in AR_LOOKUPS

G4 --> Contact reference for insert is already defined in RA_CONTACTS

G5 --> Contact reference for update is not defined in RA_CONTACTS

G6 --> The address reference specified is not defined for this customer

G7 --> CONTACT_JOB_TITLE must be defined in AR_LOOKUPS

H1 --> Contact reference has two different first names

H2 --> Contact reference has two different last names

H3 --> Contact reference has two different titles

H4 --> Contact reference has two different job titles

H5 --> Contact reference has two different customers

H6 --> Contact reference has two different addresses

I1 --> Telephone reference has two different phone numbers

I2 --> Telephone reference has multiple extensions

I3 --> Telephone reference has two different types

I4 --> Telephone reference has two different area codes

I6 --> Telephone reference has two different customers

I7 --> Telephone reference has two different addresses

J1 --> SITE_USE_CODE is not updateable

J2 --> PRIMARY_SITE_USE_FLAG is not updateable

J3 --> LOCATION is not updateable

J4 --> CUSTOMER_TYPE is not defined in AR_LOOKUPS

J5 --> PRIMARY_SITE_USE_FLAG has an invalid value

J6 --> CUSTOMER_NUMBER must be null when auto-numbering is set to "Yes"

J7 --> CUSTOMER_NUMBER is mandatory when auto-numbering is set to "No"

J8 --> INSERT_UPDATE_FLAG has an invalid value

J9 --> CUSTOMER_STATUS must have a value of 'A' or 'I'

K1 --> Concurrent request failed

K3 --> This customer reference has two different customer types defined

L1 --> COLLECTOR_NAME is mandatory when no profile class specified

L2 --> TOLERANCE is mandatory when no profile class specified

L3 --> DISCOUNT_TERMS is mandatory when no profile class specified

L4 --> DUNNING_LETTERS is mandatory when no profile class specified

L5 --> INTEREST_CHARGES is mandatory when no profile class specified

L6 --> STATEMENTS is mandatory when no profile class specified

L7 --> CREDIT_BALANCE_STATEMENTS mandatory when no profile class specified

L9 --> DUNNING_LETTER_SET_NAME is mandatory when DUNNING_LETTERS is "Yes"

L0 --> CHARGE_ON_FINANCE_CHARGE_FLAG mandatory when INTEREST_CHARGES is "Yes"

M1 --> INTEREST_PERIOD_DAYS is mandatory when INTEREST_CHARGES is "Yes"

M3 --> COLLECTOR_NAME has an invalid value

M4 --> CREDIT_CHECKING has an invalid value

M5 --> TOLERANCE has an invalid value

M6 --> DISCOUNT_TERMS has an invalid value

M7 --> DUNNING_LETTERS has an invalid value

M8 --> INTEREST_CHARGES has an invalid value

M9 --> STATEMENTS has an invalid value

M0 --> CREDIT_BALANCE_STATEMENTS has an invalid value

N1 --> CREDIT_HOLD has an invalid value

N2 --> CREDIT_RATING has an invalid value

N3 --> RISK_CODE has an invalid value

N4 --> STANDARD_TERM_NAME which contains the payment terms has an invalid value

N5 --> OVERRIDE_TERMS has an invalid value

N6 --> DUNNING_LETTER_SET_NAME has an invalid value

N7 --> STATEMENT_CYCLE_NAME has an invalid value

N8 --> ACCOUNT_STATUS has an invalid value

N9 --> PERCENT_COLLECTABLE has an invalid value

N0 --> AUTOCASH_HIERARCHY_NAME which contains the AutoCash rule has
       an invalid value

O1 --> STATEMENT_CYCLE_NAME is mandatory when STATEMENTS is "Yes"

O2 --> LOCATION must be null when auto-numbering is set to "Yes"

O3 --> LOCATION is mandatory when auto-numbering is set to "No"

O4 --> CREDIT_CHECKING is mandatory when profile class is null

O5 --> CHARGE_ON_FINANCE_CHARGE_FLAG must be null if INTEREST_CHARGES is No

O6 --> INTEREST_PERIOD_DAYS must be null if INTEREST_CHARGES is "No"

O7 --> INTEREST_PERIOD_DAYS must be greater than zero

P1 --> Postal Code is not in the defined range of system options

Q1 --> A new location was created for a value in an address segment field

Q2 --> Validation failed for the key location flexfield structure

R1 --> CUST_SHIP_VIA_CODE is not defined in ORG_FREIGHT

R2 --> CUSTOMER_CATEGORY_CODE is not defined in AR_LOOKUPS

R3 --> CUSTOMER_CATEGORY_CODE is not enabled in AR_LOOKUPS

R4 --> CUST_TAX_CODE is not defined in AR_VAT_TAX

R5 --> CUST_TAX_REFERENCE cannot be null when CUST_TAX_CODE is 'EXEMPT'

R6 --> SITE_USE_TAX_CODE is not defined in AR_VAT_TAX

R7 --> SITE_USE_TAX_REFERENCE is required when SITE_USE_TAX_CODE is 'EXEMPT'

R8 --> Invalid demand class code.

R9 --> SITE_SHIP_VIA_CODE not defined in ORG_FREIGHT

S1 --> The customer reference specified is invalid

S2 --> The address reference specified is invalid

S3 --> The address reference specified is not valid for this customer

S4 --> Payment Method is not defined in AR_RECEIPT_METHODS

S5 --> A bank account does not exist for the specified customer

S6 --> The end date specified cannot be before the start date

S7 --> The address specified must have an active BILL_TO site defined

T1 --> Customer payment method already active between the dates specified

T2 --> Customer site payment method already active between the dates specified

T3 --> Customer already has a primary payment method for specified dates

T4 --> Customer site has a primary payment method on the dates specified

T5 --> This customer payment method is already active in this date range

T6 --> Multiple primary payment methods defined

V2 --> The bank account specified must be of type 'EXTERNAL'

V3 --> Customer bank account is already active between the dates specified

V4 --> Customer site bank account already active between these dates

V5 --> This customer already has primary bank account for specified dates

V6 --> Customer site can have only 1 primary bank account for the dates
       specified

V7 --> Duplicate rows exist in Interface table for this Customer Bank and
       date run

V8 --> Duplicate primary customer banks defined within the interface table

W1 --> BANK_NAME is mandatory when creating a new bank account

W2 --> BANK_BRANCH_NAME is mandatory when creating a new bank account

W3 --> BANK_ACCOUNT_CURRENCY_CODE is mandatory creating a new bank account

W4 --> BANK_ACCOUNT_CURRENCY_CODE is not defined in FND_CURRENCIES

W5 --> Bank number already exists.

W6 --> Duplicate bank number in interface table.

W7 --> Primary flag should be 'Y' or 'N'.

W8 --> Duplicate bank and branch name in interface table.

W9 --> Duplicate Location

W0 --> Bank and branch name already exists.

X1 --> AUTO_REC_INCL_DISPUTED_FLAG mandatory when profile class is null

X2 --> TAX_PRINTING_OPTION is mandatory when no profile class specified

X3 --> GROUPING_RULE_NAME is mandatory when no profile class is specified

X4 --> CHARGE_ON_FINANCE_CHARGES_FLAG has an invalid value

X5 --> GROUPING_RULE_NAME has an invalid value

X6 --> CURRENCY_CODE has an invalid value

X7 --> CREDIT_BALANCE_STATEMENTS is mandatory when STATEMENTS is "Yes"

X8 --> CREDIT_BALANCE_STATEMENTS must be "No" when STATEMENTS is "No"

X9 --> STATEMENT_CYCLE_NAME must be null when STATEMENTS is "No"

X0 --> OVERRIDE_TERMS is mandatory when no profile class is specified

Y1 --> PARTY_NUMBER must be null when auto-numbering is set

Y2 --> PARTY_NUMBER is mandatory when auto-numbering is set to "No"

Y3 --> Party Number already assigned to a different party.

Y4 --> This party reference has two different party numbers defined in
       RA_CUSTOMERS_INTERFACE.

Y5 --> PERSON_FLAG has an invalid value

Y6 --> Party Site Number already assigned to a different address

Y7 --> Address reference has two different party site numbers defined in
       RA_CUSTOMERS_INTERFACE.

Y8 --> PARTY_SITE_NUMBER must be null when auto-numbering is set

Y9 --> PARTY_SITE_NUMBER is mandatory when auto-numbering is set to "No"

Z1 --> CREDIT_BALANCE_STATEMENTS must be null when STATEMENTS is null

Z2 --> STATEMENT_CYCLE_NAME must be null when STATEMENTS is null

Z3 --> CHARGE_ON_FINANCE_CHARGE_FLAG must be null when INTEREST_CHARGES is null

Z4 --> INTEREST_PERIOD_DAYS must be null when INTEREST_CHARGES is null

Z5 --> DISCOUNT_GRACE_DAYS must be null when DISCOUNT_TERMS is null

Z6 --> DISCOUNT_GRACE_DAYS must positive

Z7 --> DISCOUNT_GRACE_DAYS must be null when DISCOUNT_TERMS is "No"

Z8 --> DUNNING_LETTER_SET_NAME must be null when DUNNING_LETTERS is "No"

Z9 --> DUNNING_LETTER_SET_NAME must be null when DUNNING_LETTERS is null

Z0 --> CURRENCY_CODE is mandatory when a profile amount value is populated

a1 --> Customer record for insert must have validated profile record defined

a2 --> TAX_PRINTING_OPTION has an invalid value

a3 --> The customer profile for this customer reference already exists

a4 --> The customer profile class for update does not exist

a7 --> Duplicate record within the interface table

a8 --> Conflicting profile classes specified for this customer/site

b1 --> Both TRX_CREDIT_LIMIT and OVERALL_CREDIT_LIMIT must be populated

b2 --> TRX_CREDIT_LIMIT may not be greater than the OVERALL_CREDIT_LIMIT

b3 --> DUNNING_LETTER_SET_NAME must have a unique value

b4 --> COLLECTOR_NAME must have a unique value

b5 --> STANDARD_TERM_NAME must have a unique value

b6 --> STATEMENT_CYCLE_NAME must have a unique value

b7 --> BANK_ACCOUNT_NUM is mandatory when creating a new bank account

b8 --> AUTO_REC_INCL_DISPUTE_FLAG has an invalid value

b9 --> PAYMENT_GRACE_DAYS must be a positive value

e2 --> Bill_to_orig_address_ref should only be defined for Ship-to Addresses

e3 --> Bill_to_orig_address_ref is not a valid bill-to address

f1 --> You may have only one active Dunning site use for each customer

f2 --> For each customer, you may only have one active "Statements" type

f3 --> For each customer, you may only have one active Legal site

f4 --> Clearing Days must be greater than or equal to zero

f5 --> Address language is not installed

f6 --> Address reference has different languages

f7 --> Duplicate telephone reference in table RA_CONTACT_PHONES_INTERFACE

f8 --> A bank and branch with this bank number and branch number already exists

f9 --> Customer Prospect Code must be either CUSTOMER or PROSPECT

g1 --> This customer reference has two different customer prospect codes

u5 --> Contact reference has two different e-mail addresses

w2 --> CREDIT_CLASSIFICATION must have a valid value

w3 --> You cannot update the PARTY_TYPE using Customer Interface.
       Please do not specify a value for PARTY_TYPE when the
       INSERT_UPDATE_FLAG is set to U.

w4 --> When you create a PERSON party_type, you must provide
       PERSON_FIRST_NAME or PERSON_LAST_NAME.

y0 --> CONTACT_JOB_TITLE is not defined

y1 --> PHONE_COUNTRY_CODE is not defined in HZ_PHONE_COUNTRY_CODES

y2 --> This customer is already assigned to a different party

y3 --> This customer is already assigned to a different party

y4 --> LOCKBOX_MATCHING_OPTION must have a valid value

y6 --> TELEPHONE_TYPE cannot be updated from telex to any other type or any
       other type to telex.

y7 --> You cannot update this address. A printed, posted, or applied
       transaction with an associated tax line exists for this address

y8 --> ADDRESS_CATEGORY_CODE does not exist. Please enter a valid adress
       category code or define a new one using the Receivables Lookups
       window.

y9 --> ADDRESS_CATEGORY_CODE is not enabled. Please enable this address
       category by updating the Enabled flag in the Receivables Lookups
       window.

Query to list All User Roles in Oracle ERP Cloud Fusion

SELECT *
FROM
  (SELECT user_info.user_login,
    user_info.user_id,
    user_roles.role_name,
    user_info.creation_date,
    user_info.first_name,
    user_info.last_name,
    user_info.location_code,
    user_info.location_name,
    user_info.town,
    user_info.country,
    user_info.department,
    user_info.username,
    user_info.active_flag
  FROM
    (SELECT DISTINCT pp.creation_date creation_date,
      ppf.first_name first_name,
      ppf.last_name last_name,
      hl.location_code location_code,
      hl.location_name location_name,
      hl.town_or_city town,
      hl.country country,
      pd.name department,
      pu.username username,
      pu.active_flag active_flag,
      au.user_id user_id,
      au.user_login user_login
    FROM per_persons pp,
      per_all_people_f papf,
      per_person_names_f_v ppf,
      hr_locations_all_f_vl hl,
      per_departments pd,
      per_all_assignments_m paaf,
      per_users pu,
      ase_user_vl au
    WHERE au.user_guid          = pu.user_guid(+)
    AND pu.person_id              = papf.person_id(+)
    AND papf.person_id           = pp.person_id(+)
    AND pp.person_id            = ppf.person_id(+)
    AND ppf.person_id       = paaf.person_id(+)
    AND paaf.location_id     = hl.location_id(+)
    AND paaf.organization_id = pd.organization_id(+)
    AND TRUNC(sysdate) BETWEEN NVL(ppf.effective_start_date,TRUNC(sysdate)) AND NVL(ppf.effective_end_date,TRUNC(sysdate))
    AND TRUNC(sysdate) BETWEEN NVL(papf.effective_start_date,TRUNC(sysdate)) AND NVL(papf.effective_end_date,TRUNC(sysdate))
    AND TRUNC(sysdate) BETWEEN NVL(paaf.effective_start_date,TRUNC(sysdate)) AND NVL(paaf.effective_end_date,TRUNC(sysdate))
    AND TRUNC(sysdate) BETWEEN NVL(hl.effective_start_date,TRUNC(sysdate)) AND NVL(hl.effective_end_date,TRUNC(sysdate))
    AND TRUNC(sysdate) BETWEEN NVL(pd.effective_start_date,TRUNC(sysdate)) AND NVL(pd.effective_end_date,TRUNC(sysdate))
    AND TRUNC(sysdate) BETWEEN NVL(au.effective_start_date,TRUNC(sysdate)) AND NVL(au.effective_end_date,TRUNC(sysdate))
    )user_info
  LEFT JOIN
    (SELECT u.user_login user_login,
      r.role_name role_name,
      r.description description ,
      aurm.user_id user_id
    FROM ase_user_vl u,
      ase_role_vl r,
      ase_user_role_mbr aurm
    WHERE r.role_id              = aurm.role_id
    AND aurm.user_id             =u.user_id
    AND r.effective_end_date    IS NULL
    AND aurm.effective_end_date IS NULL
    )user_roles
  ON user_info.user_login=user_roles.user_login
  ) qrslt
WHERE 1 = 1
  AND UPPER(QRSLT.username) = UPPER('abcd@oracletechcenter.blogspot.com')
ORDER BY user_login

BARCODE IN XML PUBLISHER REPORTS ORACLE R12

BARCODE IN XML PUBLISHER

1.     Client Setup

·         Get the font IDAutomation font from idautomation

·         Place the IDAutomation font under c:\Windows\Fonts.

·         Select IDAutomation font for Barcode fields in XML Publisher Template.

·         Calling encoder in the template.(Only if vendor specific fonts and java encoder is used else ignore)

·         Add following expression in your template, It can be added directly to template or as a value to Form Field.

<?register-barcode-vendor:'oracle.apps.xdo.template.rtf.util.barcoder.BarcodeUtilaaa';'XMLPBarVendor'?>

·         Add format-barcode syntax to barcode field. Replace BARCODE in below syntax with your xml field.

*<?format-barcode:BARCODE;'code128a';'XMLPBarVendor'?>*

2.     Server Setup -- Only needed if you have vendor specific barcode fonts else ignore.

If vendor specific fonts are used, java encoder will be provided along with font which will be recognized by external device.

Below imports have to be added to the vendor provided java encoder.

  package oracle.apps.xdo.template.rtf.util.barcoder;

import java.util.Hashtable;

import java.lang.reflect.Method;

import oracle.apps.xdo.template.rtf.util.XDOBarcodeEncoder;

import oracle.apps.xdo.common.log.Logger;

// This class name will be used in the register vendor field in the template.

public class BarcodeUtil implements XDOBarcodeEncoder

// The class implements the XDOBarcodeEncoder interface

{

// This is the barcode vendor id that is used in the register vendor field and

// format-barcode fields

public static final String BARCODE_VENDOR_ID = "XMLPBarVendor";

// The hastable is used to store references to the encoding methods

public static final Hashtable ENCODERS = new Hashtable(10);

// The BarcodeUtil class needs to be instantiated

public static final BarcodeUtil mUtility = new BarcodeUtil();

// This is the main code that is executed in the class, it is loading the methods

// for the encoding into the hashtable. In this case we are loading the three code128

// encoding methods we have created.

static {

try {

Class[] clazz = new Class[] { "".getClass() } ;

ENCODERS.put("code128a",mUtility.getClass().getMethod("code128a", clazz));

ENCODERS.put("code128b",mUtility.getClass().getMethod("code128b", clazz));

ENCODERS.put("code128c",mUtility.getClass().getMethod("code128c", clazz));

} catch (Exception e) {

// This is using the XML Publisher logging class to push errors to the XMLP log file.

Logger.log(e,5);

}

}

// The getVendorID method is called from the template layer at runtime to ensure the correct

// encoding method are used

public final String getVendorID()

{

return BARCODE_VENDOR_ID;

}

// The isSupported method is called to ensure that the encoding method

// called from the template is actually present in this class. If not

// then XMLP will report this in the log.

public final boolean isSupported(String s)

{

if(s != null)

return ENCODERS.containsKey(s.trim().toLowerCase());

else

return false;

}

// The encode method is called to then call the appropriate encoding method,

// in this example the code128a/b/c methods.

public final String encode(String s, String s1)

{

if(s != null && s1 != null)

{

try

{

Method method = (Method)ENCODERS.get(s1.trim().toLowerCase());

if(method != null)

return (String)method.invoke(this, new Object[] {

s

});

else

return s;

}

catch(Exception exception)

{

Logger.log(exception,5);

}

return s;

} else

{

return s;

}

}

/** Add Vendor Method for Code128a */

public static final String code128a( String DataToEncode )

{

return Printable_string;

}

}

 

 Generate class file from java code and place it under OA_JAVA/oracle/apps/xdo/template/rtf/util/barcoder. If barcoder directory doesn't exist create one.

$ cd $OA_JAVA/oracle/apps/xdo/template/rtf/util

$ mkdir barcoder

 Change permissions of the barcoder directory to 777/755.

3.     XML Publisher Font Setup

No longer XML publisher fonts needed to be placed on the server. They can be uploaded and used from XML publisher font file and font mappings.

Navigate to XML Publisher responsibility.

Go to Administration Tab.

Click on Font Files and create font file

HERE THE FONT IS IDAutomationSHcC128M

Font Name: IDAutomationSHcC128M

File: Select IDAutomationSHcC128M.ttf from your saved location.

Click Apply.

Click on Font Mappings. Click “Create Font Mapping Set”.

Mapping Name: IDAutomationSHcC128M

Mapping Code: IDAutomationSHcC128M

Type: FO To Pdf

Click Apply.

Click on Create Font Mapping. Fill the values as below screen shot.

Font Family: IDAutomationSHcC128M

Font Value: IDAutomationSHcC128M

Click Apply.

In the next page enter

Click on Administration Tab

Expand FO Processing and enter Barcodes as Font Mapping Set value.